vBulletin search engine to MySQL Fulltext

[under2233]

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.

[Jake Bunce]

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

[Steve Machol]

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;

[under2233]

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

[Steve Machol]

Then you will need to run those queries again.

[Freddie Bingham]

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.

[under2233]

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.

 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.

[under2233]

up and waiting ....

[Steve Machol]

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.