Catalog Search Index refresh running slow or halting/freezing

A lot of people are starting to find that with large catalogues that the new indexing manager in Magento 1.4 may be starting to time out when generating Catalog Search Index - Rebuild Catalog product fulltext search index. This usually results in a blank screen when refreshing indexes or a index timeout error

This is largely due to a number of factors:

• the number of products in a catalogue
• the number of store views
• the apache/lighttpd timeout setting
• the php.ini maximum execution time & script input time
• an already inflated catalogsearch_fulltext MySQL table
• the lock file is still place from the previous time it ran preventing it running again

To remedy this issue, you'll need to know a few facts. The catalogsearch_fulltext table is essentially re-built entirely when running this command. BUT, if you once had multiple store views (or forgot the delete the default French/German views), the table will contact a record for each product PER store view. So it is always best to truncate catalogsearch_fulltext before starting. Either log in via MySQL command line and run

truncate catalogsearch_fulltext;

or log into phpMyAdmin and hit the empty link.

For 1 store view with a dedicated server (2.5GHz Quad core), it takes an average of 0.003 minutes per product to insert the fulltext field.

So in our example, we have a store with 200,000 SKUs and a single store view, so 200,000 (skus) * 0.003 (time p/p) * 1 (store view) * 60 = 36000 seconds

Which means it would be silly to execute the command via the web server, it would be preferred to run a command line executable. The new index manager assigns a "process ID" to each type of index:

1. Product Attributes Index product attributes for layered navigation building
2. Product Prices Index product prices
3. Catalog Url Rewrites Index product and categories url rewrites
4. Product Flat Data Reorganize EAV product structure to flat structure
5. Category Flat Data Reorganize EAV category structure to flat structure
6. Category Products Indexed category/products association
7. Catalog Search Index Rebuild Catalog product fulltext search index
8. Stock status Index product stock status

In our case, number 7 is the offending (read: slow) command. So we can run this manually using a command line script instead. Change your php.ini for your php-cli installation to suit the maximum time out shown above (36000 seconds for us).

Then create a file in the root directory of your Magento installation, we'll call it fulltext.php:

getConnection('core_write'); 
$mysqli->query($sql); 
$process = Mage::getModel('index/process')->load(7); 
$process->reindexAll(); 
Mage::log("Finished Rebuilding Search Index At: " . date("d/m/y h:i:s")); 

Then it is as simple as logging in through SSH and running:

php fulltext.php

Sit back and drink your coffee - as, on a large catalogue, this can take a while!