Switch to Fulltext Search, some questions

[cirdok2]

Hi all,
following a previous topic I decided to switch to the boolean fulltext search.

My database is now 900Mb (over 1.4 millions posts), with:

Postindex 411 Mb (data 203, index 208)
Post 367Mb (data 316, index 51)

1) I have installed MySQL 4.1.20 and php 4.3.9, is that ok?
2) do you know moreoless how much space would this actually free up?
3) do you know how long it should approx take?
4) is better to turn off the board during the process? I'd like also to Empty postindex and word tables

Any advise will be highly appreciate.

Thanks!

[cirdok2]

I forgot the 5th

5) postindex and word tables will be emptied after the tables for the mySQL Fulltext Search will be created and build?

I need to know how much additional space I need to complete all the process "safely".

Thanks

[Wayne Luke]

1) This should be fine.
2) Fulltext search really isn't about freeing up space but about changing where the processing goes. You'll just be trading one index for another.
3) Depends on your server really. Hard to determine. Most sites that I have done were completed in less than 15 minutes. They didn't have the posts you have though. You might have to run the queries from the MySQL command line instead of in the Control Panel.
4) Yes. You should turn off the forums during any major database operations to lower to likelihood of data corruption. I would suggest using mysqlhotcopy to make a backup of your database before you start as well.
5) Once done you can empty these tables.

As far as space needed, you should have 2 times the size of your post table available in your temporary partition at the minimum. More is better.

[iardon]

Fulltext search really isn't about freeing up space but about changing where the processing goes.

Would you mind expanding on that? I've been torn for quite a while as to whether it's best to keep vb search or switch to fulltext.

It doesn't seem like VB is advocating for everyone to switch to fulltext, yet they (vb staff) do always praise it. So....

[Wayne Luke]

One of the most processor intensive things in computing is searching. Making sure it returns decent results is even more intensive.

Basically with the vBulletin search, we use PHP to:

1. Check for words either too long or too short and toss them
2. Check for any common words and remove them,
3. Make sure the resulting words exist somewhere (check the word table)
4. Build a query to check which posts contain the words (postindex table)
5. Send the query to MySQL.
6. Sort the results and paginate them and display them.

With Fulltext search we use PHP to:

1. Build the query.
2. Send it to MySQL
3. Paginate and display the results.

MySQL will check and see if the word is too common to be indexed. Or if it is too long or short. It also returned its results with relevancy to the original query that makes things easier to sort and display.

There are also savings when inserting a new post. With Fulltext you just insert it and MySQL builds the indices for the post. With vBulletin's search, we have to break the post apart into its component words, toss the words that are too short or too long, toss the common words, check the word table to see if the remaining words already exist and then write dozens of little records into the postindex table so we know later where those words reside. Myisam tables do not allow row-level locking, and this can cause problems on large boards with locking the postindex table frequently.

MySQL also uses compiled C++ libraries to do the sorting and indexing so it is also faster and can be optimized a lot more than PHP routines can. In most cases, using Fulltext search uses less resources in the long run. It also makes the database easier to maintain because your are using native structures instead of indexing via tables.

[iardon]

Thanks for that great write up.

On a shared server where one doesn't have control over Mysql and PHP settings, is there something one should confirm with the host before switching to FullText?

Also, just to confirm, the below statement is regarding FullText, right?

Myisam tables do not allow row-level locking, and this can cause problems on large boards with locking the postindex table frequently.

[Wayne Luke]

It refers to vBulletin's search actually... Fulltext doesn't use the postindex table at all.

As far as verifying with your host, you should ask them if they support fulltext searching (don't think you can turn it off in MySQL though) and what their limits are with it.