Catalog Search Index refresh running slow or halting/freezing

Posted on April 29, 2010 by Benjamin

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:

<?php
require_once 'app/Mage.php';
umask( 0 );
Mage :: app( "default" );
Mage::log(”Started Rebuilding Search Index At: ” . date(”d/m/y h:i:s”));
$sql = "truncate catalogsearch_fulltext;";
$mysqli = Mage::getSingleton('core/resource')->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!

This entry was posted in Magento and tagged blank screen when refreshing indexes, catalogsearch_fulltext, index timeout error, magento Catalog Search Index, magento fulltext, magento index blank screen, magento index slow. Bookmark the permalink.

87 Responses to Catalog Search Index refresh running slow or halting/freezing

Benjamin
16 Feb. 2011

The extension has now been updated (on Magento Connect) to include a patch to fix the slow Category/Products index.

Reply
- werner
  16 Feb. 2011
  
  Nice tool thanks
  is it up to date for magento 1.5.0.1
  
  Reply
  - Benjamin
    16 Feb. 2011
    
    Its untested in 1.5.0.1 – but you are more than welcome to try!
    
    Reply
    - werner
      16 Feb. 2011
      
      I just did but i am getting this error
      a:5:{i:0;s:125:”SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘digitalf_TQtOgma.catalog_category_product_index_idx’ doesn’t exist”;i:1;s:1661:”#
      
      i am not handy with this database errors
      
      greetings werner
      
      Reply
Sumon
23 Feb. 2011

Not stable, before launching software please test properly.

I am using 1.4.1.1 table not found errors.

Reply
- Benjamin
  23 Feb. 2011
  
  The extension is stable, but it sounds like you haven’t tried running the index via Magento so that it can apply the indexes to the temporary table – or that you have prefixed your Magento table names.
  
  Reply
Richard
24 Feb. 2011

Thanks for the great extension, it fixed the problem with the search index immediately! I have had a small problem trying to run the category products index, I just get an error page when I click the button. Am I doing something wrong, or is there an easy fix for the problem please? Thanks again though, the search index has been giving me a headache for 3 days now!

Reply
- Benjamin
  24 Feb. 2011
  
  Hi Richard,
  
  Have you prefixed your Magento table names?
  
  The button can only do its job if the temporary table exists, ie. if you have started the main indexing process in Magento and it never completes.
  
  Reply
Richard
24 Feb. 2011

I remember being asked if I wanted to add a prefix to the table names when I installed magento, I didn’t bother, I wasn’t sure of the point of it at the time so I left the field empty. Did I do wrong?

Reply
Rafael
8 Apr. 2011

Thanks, works great with 1.4.2
Saved me a lot of time

Reply
Nik Linders
14 Apr. 2011

Great extension! It doesn’t work with 1.5.0.1 yet, though.
Any plans on testing and upgrading this to 1.5.0.1?

Details: On 1.5.0.1, after installing this, the index management page is empty.
That is, I get only the top admin menu and footer, but I can’t see the indexers anymore.

Thanks!

Reply
Wiliam Gardner
22 Jun. 2011

I’m running on 1.4.1.1 – I’ve started the index process (the orange button says ‘Processing’), and when I try to manually refresh I get a time execution error.

After installing the extension, I get the following error when I press the Sonassi button:

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘therugsw_magento_tm.index_process’ doesn’t exist

Any ideas? I have not prefixed my tables.

Reply
Marc
6 Jul. 2011

Thank you for the code above, could you tell me how to alter it to reindex the product prices? according to your post is it process id 2.

thank you very much!

Reply
Darin Densley
14 Jul. 2011

I have installed this through Magento Connect but it is not showing up on my Index Manager. I really need this it is exactly what I have been looking for. Any ideas what I may be doing wrong? I have tried it twice just to check myself.

Reply
DVS
28 Jul. 2011

We have magento 1.4.2.0, and our category products index has been taking 12+ hours to complete via SSH!!! But our catalog search index only takes a few minutes… will this extension help with the category product index for 1.4.2.0 ?

Reply
- Benjamin
  28 Jul. 2011
  
  I’m afraid not.
  
  No index should take 12 hours to complete unless either it has stalled, or your have 200k+ SKUs.
  
  Reply
  - DVS
    28 Jul. 2011
    
    Ah.. dang, reason why it takes so long is because A.) we have multiple store views, B.) our original developer/designer structured our site in a way where we have over 10,000 categories… yeah.. we’re working to find a solution at the moment :-/ Thanks for the reply
    
    Reply
Ivan
28 Jul. 2011

thanks! My Magento ver. 1.5.1.0 , just run the extension , it works well, products are searchable now!

Reply
Jens
4 Aug. 2011

Hi
I get the follwing error:

PHP Fatal error: Uncaught exception ‘Mage_Core_Model_Store_Exception’ in /var/www/storename/storename/wwwroot/app/code/core/Mage/Core/Model/App.php:1291
Stack trace:
#0 /var/www/storename/storename/wwwroot/app/code/core/Mage/Core/Model/App.php(803): Mage_Core_Model_App->throwStoreException()
#1 /var/www/storename/storename/wwwroot/app/code/core/Mage/Core/Model/App.php(454): Mage_Core_Model_App->getStore()
#2 /var/www/storename/storename/wwwroot/app/code/core/Mage/Core/Model/App.php(262): Mage_Core_Model_App->_initCurrentStore(‘default’, ‘store’)
#3 /var/www/storename/storename/wwwroot/app/Mage.php(570): Mage_Core_Model_App->init(‘default’, ‘store’, Array)
#4 /var/www/storename/storename/wwwroot/f.php(4): Mage::app(‘default’)
#5 {main}
thrown in /var/www/storename/storename/wwwroot/app/code/core/Mage/Core/Model/App.php on line 1291

Can you help me out?
Fulltext isn’t working at all.

Reply
Dan
13 Aug. 2011

This has just fixed a 1.4.2.0 site I have been having problems with in two clicks, great extension!

Reply
beyonce lace front wigs
18 Aug. 2011

is it use for magento 1.5 ? when i reindex thd Catalog Search Index, i get “There was a problem with reindexing process.”

Reply
- Benjamin
  20 Aug. 2011
  
  It shouldn’t be necessary for 1.5, its for =<1.4
  
  Reply
mutaaly
19 Aug. 2011

installed but get following error ; There has been an error processing your request

SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn’t match value count at row 1

Trace:
#0 /home/mhtrends/public_html/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/mhtrends/public_html/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 /home/mhtrends/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(‘INSERT INTO cat…’, Array)
#3 /home/mhtrends/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query(‘INSERT INTO cat…’, Array)
#4 /home/mhtrends/public_html/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(159): Varien_Db_Adapter_Pdo_Mysql->query(‘INSERT INTO cat…’)
#5 /home/mhtrends/public_html/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Category/Indexer/Product.php(559): Mage_Index_Model_Mysql4_Abstract->insertFromSelect(‘SELECT? …’, ‘catalog_categor…’, Array)
#6 /home/mhtrends/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(125): Mage_Catalog_Model_Resource_Eav_Mysql4_Category_Indexer_Product->reindexAll()
#7 /home/mhtrends/public_html/app/code/core/Mage/Index/Model/Process.php(139): Mage_Index_Model_Indexer_Abstract->reindexAll()
#8 /home/mhtrends/public_html/app/code/community/Sonassi/FastSearchIndex/controllers/AdminController.php(61): Mage_Index_Model_Process->reindexAll()
#9 /home/mhtrends/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Sonassi_FastSearchIndex_AdminController->refreshCatProdAction()
#10 /home/mhtrends/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(253): Mage_Core_Controller_Varien_Action->dispatch(‘refreshCatProd’)
#11 /home/mhtrends/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#12 /home/mhtrends/public_html/app/code/core/Mage/Core/Model/App.php(304): Mage_Core_Controller_Varien_Front->dispatch()
#13 /home/mhtrends/public_html/app/Mage.php(596): Mage_Core_Model_App->run(Array)
#14 /home/mhtrends/public_html/index.php(80): Mage::run(”, ‘store’)
#15 {main}
what’s reason of the error , ver; 1.4.2.0 magento .
Thank you

Reply

Catalog Search Index refresh running slow or halting/freezing

87 Responses to Catalog Search Index refresh running slow or halting/freezing

Leave a Reply Cancel reply

Categories

Archives

We’re against PROTECT IP Act (PIPA) – are you?