Enable MySQL Fulltext search on 5.6 gig post table

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • hichew
    Senior Member
    • Dec 2003
    • 158

    Enable MySQL Fulltext search on 5.6 gig post table

    Any suggestion to move from vB default search to Fulltext search on 5.6 gig post table?

    There's no way using admincp to do this task (browser will time out)

    Seems like I should do this using SSH meanwhile I turn off the forum.

    ALTER TABLE post ADD FULLTEXT INDEX (title,pagetext)
    ALTER TABLE thread ADD FULLTEXT INDEX (title)

    Then what's next?
  • Steve Machol
    Former Customer Support Manager
    • Jul 2000
    • 154488

    #2
    You can manually run those queries in MySQL, then after that empty (don't delete) your postindex and word tables.
    Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
    Change CKEditor Colors to Match Style (for 4.1.4 and above)

    Steve Machol Photography


    Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


    Comment

    • hichew
      Senior Member
      • Dec 2003
      • 158

      #3
      can you guess.. how long it takes to run that queries? (5.6 gb)
      should I turn off the board?

      and when the queries done.. how to make vBulletin use fulltext search ? (is it automatic? or i need to make some changes in admincp?)

      Comment

      • Steve Machol
        Former Customer Support Manager
        • Jul 2000
        • 154488

        #4
        Once those queries are done you should be set. I have no idea how long this will take. A lot depends on your server. It could take hours. It will go faster if you have your forums turned off when running those queries.
        Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
        Change CKEditor Colors to Match Style (for 4.1.4 and above)

        Steve Machol Photography


        Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


        Comment

        • hichew
          Senior Member
          • Dec 2003
          • 158

          #5
          btw how vB know that I'm using fulltext search by just doing that 2 queries without telling / set something in vB admin to use fulltext search instead of vB search?

          ALTER TABLE post ADD FULLTEXT INDEX (title,pagetext)
          ALTER TABLE thread ADD FULLTEXT INDEX (title)

          Comment

          • Steve Machol
            Former Customer Support Manager
            • Jul 2000
            • 154488

            #6
            Running those queries tells vB that you are using fulltext.
            Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
            Change CKEditor Colors to Match Style (for 4.1.4 and above)

            Steve Machol Photography


            Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


            Comment

            • hichew
              Senior Member
              • Dec 2003
              • 158

              #7
              Ahhh... even I do it manually in ssh ? ( sorry keep asking )

              Comment

              • Steve Machol
                Former Customer Support Manager
                • Jul 2000
                • 154488

                #8
                Yes, it should recognize the fullltext indices after they have been created.
                Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
                Change CKEditor Colors to Match Style (for 4.1.4 and above)

                Steve Machol Photography


                Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


                Comment

                • hichew
                  Senior Member
                  • Dec 2003
                  • 158

                  #9
                  last question (i hope)...
                  what happen if the query failed (post) table....
                  it's had been running for 80 minutes... (in ssh mode) and i can't tell if mysql processing the query.

                  is there anyway I can tell if the query is running?

                  Comment

                  • Steve Machol
                    Former Customer Support Manager
                    • Jul 2000
                    • 154488

                    #10
                    I don't know how to tell. Perhaps checking active mysql processes. It could conceivably take hours to run these on a post table that size.
                    Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
                    Change CKEditor Colors to Match Style (for 4.1.4 and above)

                    Steve Machol Photography


                    Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


                    Comment

                    • hichew
                      Senior Member
                      • Dec 2003
                      • 158

                      #11
                      ok the query completed..
                      now the vB is running on fulltext search..

                      but when I do the search.. i keep getting :

                      Sorry - no matches. Please try some different terms. The following words are either very common, too long, or too short and were not included in your search : smoked



                      And on admincp... (click Search Type) it still :

                      Your forum is currently using the vBulletin search engine.

                      Comment

                      • slappy
                        Senior Member
                        • Apr 2003
                        • 1206

                        #12
                        Have you logged out of the adminCP and logged back in and re-checked the option?

                        Also have you run the maintenance tab to Repair/Optimize Tables a few times? Have you rebuilt the Search Index?

                        Regards,
                        Slappy

                        Comment

                        • hichew
                          Senior Member
                          • Dec 2003
                          • 158

                          #13
                          ooo right.... it's works!!
                          thanks slappy

                          Comment

                          • cirdok2
                            Senior Member
                            • Aug 2004
                            • 412
                            • 3.6.x

                            #14
                            I have to upgrade to 3.6.8 and I'd like to switch from vB default search to Fulltext search (>1Gb table) too.

                            When is the best time to do the switch? Before, after or during the upgrading process?


                            Thanks

                            Comment

                            • hichew
                              Senior Member
                              • Dec 2003
                              • 158

                              #15
                              i think it's better to upgrade the vB first..

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...