When running the catalogsearch_fulltext indexer on Magento 1.6 with a large number of storeviews (talking about 50+) or a lot of products, it might take a whole lot of time. We had runtimes of a few hours lately and went into analyzing where those came from. The result: The catalogsearch_fulltext table is missing an index crucial for the indexer to perform well.

When indexing, there is a DELETE query with WHERE store_id = 42 running to remove all previous cache entries. Looking at the table’s indexes, however, there is only a compound UNIQUE index on both product_id and store_id:

That does not apply when running a query using store_id to select entry, as a simple EXPLAIN SELECT shows:

To solve this we simply added another INDEX to the table (Note: this might break updatability, but that should throw errors so you might be able to trace it). I strongly recommend to TRUNCATE the table before creating the index, else it will may take some time to create it. Note that until the index is rebuilt, searches in the store will not return results!

ALTER TABLE `mage_gs_20130528`.`catalogsearch_fulltext` ADD INDEX `YOURCOMPANY_IDX_CATALOGSEARCH_FULLTEXT_STORE_ID` ( `store_id` )

After that, we have the following indexes:

And EXPLAIN shows everything is working as intended. A quick test on my localhost gave a 30x speed improvement on the SELECT, and on our servers the catalogsearch_fulltext index gets built in under 10 minutes again. Yay!

Catalogsearch_Fulltext, Index, Magento, Speed | Kommentar schreiben | Trackback einrichten