Magento: Improve speed of catalogsearch_fulltext indexer

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:

Magento Catalogsearch_Fulltext indexes

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

Magento Catalogsearch_Fulltext EXPLAIN

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:

Magento Catalogsearch_Fulltext 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!

Magento Catalogsearch_Fulltext EXPLAIN

GD Star Rating
Magento: Improve speed of catalogsearch_fulltext indexer, 4.0 out of 5 based on 2 ratings

, , , | Kommentar schreiben | Trackback einrichten

Einen Kommentar schreiben

  • Themen

  • Zeitliches Archiv

© 2006-2023 - Impressum

» Blog powered by Wordpress. Silk icons von FamFamFam.