Announcement

Announcement Module
Collapse
No announcement yet.

Enable MySQL Fulltext search on 5.6 gig post table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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?

  • #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


    • #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


      • #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


        • #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


          • #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


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

              Comment


              • #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


                • #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


                  • #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


                    • #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


                      • #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


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

                          Comment


                          • #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


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

                              Comment

                              Working...
                              X