You no longer need to follow the instructions below, you can use the new tool on Magento Connect instead.
Please note. This article applies to Magento 1.4 and greater only.
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:
- Product Attributes Index product attributes for layered navigation building
- Product Prices Index product prices
- Catalog Url Rewrites Index product and categories url rewrites
- Product Flat Data Reorganize EAV product structure to flat structure
- Category Flat Data Reorganize EAV category structure to flat structure
- Category Products Indexed category/products association
- Catalog Search Index Rebuild Catalog product fulltext search index
- 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!