Announcement

Collapse
No announcement yet.

vBulletin search engine to MySQL Fulltext

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • vBulletin search engine to MySQL Fulltext

    Hi

    I am running a forum with vbulletin 3.5.1

    106,632 members

    943,659 posts

    94,199 Threads


    Today I tried to convert from vBulletin search engine to MySQL Fulltext search using the admincp but I always got page timeout..

    So I decided to move to ssh and run the queries manually...

    I ran this first and took 2.2 minutes with no errors
    ALTER TABLE thread ADD FULLTEXT INDEX (title)


    Then I ran this query
    ALTER TABLE post ADD FULLTEXT INDEX (title,pagetext)

    This query was running for 1 hour and the server load was so high reached up to 35 % until I got my site suspended ....

    now my site is back I was wondering

    1-How can I find out where did the query reach so I don't have to start all over again and kill the server.

    Note : post tables has the two new fields pagetext and title filled all with data from postid 3 to post id 1029336.

    2- Is there a way to get back to vBulletin search and remove all the new changes knowing that I still have the postindex table ...

    I appreciate ur time and effort to solve my problem.




  • #2
    I am not sure what you would check at this point. I will ask the others about this.

    Comment


    • #3
      If I understandd you correctly the queries did complete, correct?

      If so, edit includes/config.php to include this line:

      $config['Misc']['debug'] = true;

      Then go into vBulletin->Options and you will see a new section called 'Version Info and Other Untouhables'. Change the 'Fulltext Search' setting to Yes. Now edit includes/config.php again and remove

      $config['Misc']['debug'] = true;
      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


      • #4
        Hi Steve,

        No the query was not complete please read my first post as I explained what happened .

        Meanwhile I tried ur sugessetion (config file)and tried to search for a word and here what I got:


        Database error in vBulletin 3.5.2:

        Invalid SQL:

        SELECT thread.threadid
        , post.postid


        FROM thread AS thread
        INNER JOIN post AS post ON(thread.threadid = post.threadid)
        WHERE MATCH(post.title, post.pagetext) AGAINST ('people')
        LIMIT 500;

        MySQL Error : Can't find FULLTEXT index matching the column list
        Error Number : 1191
        Date : Monday, December 19th 2005 @ 02:41:14 AM
        Script : http://.net/forum/search.php
        Referrer : http://.net/forum/index.php
        IP Address : *****
        Username : test
        Classname : vb_database

        Comment


        • #5
          Then you will need to run those queries again.
          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


          • #6
            The post table always had the fields pagetext and title. The queries you need to run add a fulltext index upon those fields. The only way for you to do this is to convince your host to allow the queries to run to completion, ignoring the load that is going to be presented on your server. Perhaps they may even execute them for you.

            Comment


            • #7
              Thank you all for your gr8 tips..


              I wonder if this solution will do the trick for me or not plz let me know if I am missing anything...


              1- backup post and thread table.
              2- Restore them in my personal computer.
              3- Run the following queries.

              PHP Code:
               ALTER TABLE post ADD FULLTEXT INDEX (title,pagetext);
              ALTER TABLE thread ADD FULLTEXT INDEX (title); 
              4- Back up the two tables again in my pc.

              5- Restore them on the server.

              6- clear postindex and wordid tables.

              7- enable the MySQL Fulltext through 'Version Info and Other Untouhables'.


              I look forward to hearing from u soon.

              Comment


              • #8
                up and waiting ....

                Comment


                • #9
                  That should be fine but make sure you have your forums turned off during all this. Also on #6, you can empty the postindex and word tables, but don't drop them.
                  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

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