Catalog Search Index refresh running slow or halting/freezing

fast_search_reindex_magento

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:

  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!

  • Sjoerd

    Hi, I tried your script. But when using ssh i am getting this error.

    Warning: require_once(app/Mage.php): failed to open stream: Inappropriate ioctl for device in /var/www/vhosts/xxx.xxx/httpdocs/web/fulltext.php on line 3
    PHP Fatal error: require_once(): Failed opening required 'app/Mage.php' (include_path='.:') in /var/www/vhosts/xxx.xxx/httpdocs/web/fulltext.php on line 3

    • HI Sjoerd,

      It sounds like you didn't put the file in your root Magento directory.

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

      By root directory, we mean the one in which the Magento installation is in (ie. the one which contains app,var etc.)

      • Sjoerd

        Hi Ben,

        The fulltext.php is in the root folder.

        • Hi Sjoerd,

          Is it in the root MAGENTO directory?

          • Sjoerd

            Yes. I build over 80 Magento shops. I know what you are talking about.

          • Hi Sjoerd,

            This isn't an issue with our script, but more with your PHP-CLI installation by the looks of things.

            Compare both your Apache php.ini with your PHP-CLI php.ini - specifically the "include_path" setting.

          • Sjoerd

            Hi Ben,

            I know a thing or 2 about Magento but this is new to me. I can find my php.ini. It is in the etc folder. But I have no idea where to find my PHP-CLI php.ini. Can it be I have PHP-CGI and not CLI?
            Anyway in the php.ini I found it says,

            ;;;;;;;;;;;;;;;;;;;;;;;;;
            ; Paths and Directories ;
            ;;;;;;;;;;;;;;;;;;;;;;;;;

            ; UNIX: "/path1:/path2"
            ;include_path = ".:/php/includes"
            ;
            ; Windows: "path1;path2"
            ;include_path = ".;c:phpincludes"

            include_path = ".:"

          • Hi Sjoerd,

            If you run this command:

            php -i | egrep --color "include_path|php.ini"
            

            It should identify both the php.ini location and the setting for include_path.

  • I got a error :

    Fatal error: Uncaught exception 'Varien_Exception' with message 'Invalid method Mage_Index_Model_Process::_getLockFile(Array ( ) )' in /home/geesangc/public_html/lib/Varien/Object.php:542 Stack trace: #0 [internal function]: Varien_Object->__call('_getLockFile', Array) #1 /home/geesangc/public_html/fulltext.php(11): Mage_Index_Model_Process->_getLockFile() #2 {main} thrown in /home/geesangc/public_html/lib/Varien/Object.php on line 542

    something wrong at there?

  • Philip

    When I try and run the script on the command line (with fulltext.php in the magento root directory) I get the following exception:

    # php fulltext.php
    07/05/10 12:23:16
    Fatal error: Uncaught exception 'Varien_Exception' with message 'Invalid method Mage_Index_Model_Process::_getLockFile(Array
    (
    )
    )' in /var/www/storesdirect.co.uk/lib/Varien/Object.php:542
    Stack trace:
    #0 [internal function]: Varien_Object->__call('_getLockFile', Array)
    #1 /var/www/storesdirect.co.uk/fulltext.php(11): Mage_Index_Model_Process->_getLockFile()
    #2 {main}
    thrown in /var/www/storesdirect.co.uk/lib/Varien/Object.php on line 542

    Any ideas why that might be? I am using Magento version1.4.0.1.

    • Hi Philip,

      It could be a number of different things. Could you see if the lock file exists in:

      ./var/lock/

      For the index you are running the script on

      • Philip

        Hi Ben,

        There was a lock file in ./var/lock/ for the index but removing it makes no difference; the same exception is shown when running the script.

    • The problem here is that Ben’s code is calling a protected class method, and will throw this error. Also, the calls to _getLockFile and flock are actually uneeded, as reindexAll creates the lock file for the indexer when it’s called.

      I would also recommend calling “php -f fulltext.php&” to start the script, which will put it immediately in the background and list it’s job number and PID. Then, (assuming you’re using the bash shell) calling “disown ” so you won’t have to worry about your SSH connection terminating and thus killing the script execution before it’s had time to complete.

      Here is a modified and working version of Ben’s code. You should notice that I changed the calls to echo to Mage::log calls, that’s because the echo commands won’t print anything in a disowned process, just make sure you have logging enabled in your Magento config if you need this info.

      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”));
      
      • Hi David,

        Thanks for the comments. Sorry, we we're being a little oblivious as we have our own index module - with public, rather than protected functions for those mentioned above (hence our compatibility). The fUNLock was purely to override any "floating" files left over.

        I'll give your code a test and update the post 😉

      • Stefy

        Can you post the complete code modified please??
        Thanks

  • depa82

    Hi I created the script and launched
    http://www.mywebsite.com/fulltext.php but it show me only the start date (and don't trunc the table)....
    What's wrong?
    Thanks
    Stefy

  • Can anyone confirm full working code?

    Thanks!

    Rich

    • Hi Rich, it has been posted for you 🙂

      • Hi Ben - can you post the code here so the rest of us can get to it?

        Thx

  • Hi Ben, many thanks, but I get:

    Parse error: syntax error, unexpected T_STRING in /var/sites/o/xxxx/public_html/fulltext_search_index_cli.php on line 5

    When I run this from CLI...

    Kind regards

    Rich

  • Stefy

    Something work but i have this error :

    PHP Fatal error: Uncaught exception 'Exception' with message 'Warning: Division by zero in /home/magento/public_html/fulltext.php on line 8' in /home/magento/public_html/app/code/core/Mage/Core/functions.php:245

    My line 8 is:
    $process = Mage::getModel(’index/process’)->load(7);

    Please help help help!!!

  • dan

    is there anyway to solve this problem without ssh access? the problem is that the Catalog Search Index is stuck on 'processing'. any help is appreachiated.

  • Chaps, I'm now getting:

    SQLSTATE[42S22]: Column not found: 1054 Unknown column 'is_searchable' in 'where clause'

    Anyone seen something like this before?

    Rich

  • Ran #php fulltext.php with no error but I can't see that it did anything.

    How do I know if it is working?

  • G

    This is the most beautiful peace of code, that worked as miracle. I really thank ben for putting this online.

    Needed to a little bit of tweak, but worked amazingly after that.

  • Thanks! I created the file as suggested and first ran from the browser to make sure it was running correctly. It worked but of course timed out. I then logged in via SSH and ran the file. I had to cd to my public_html folder and then just run "php fulltext.php" no "#".

    The script worked and my catalog search fulltext index actually finished! I have 17,000 skus on a shared server.

    Thanks again!
    Nick Cron
    Medical Delivered (www.medicaldelivered.com)

  • RichPC

    Hi Ben,
    Thanks for the article. I'd just done a 46,000 sku import and this index was taking forever to refresh.

    To speed it up I went to the database table you mentioned 'catalogsearch_fulltext' and dropped the fulltext index from the 'data_index' field. Then ran magneto's refresh on the index (via admin panel not cli). Then put the fulltext index back on the 'data_index' field. Doing this made it hundreds of times faster.

    Cheers,
    RichPC

    • Hi Rich,

      Glad it helped, these little scripts can be lifesavers!

  • RichPC

    Sorry, had a false success from that dropping of the fulltext index, it was running faster because there was less data to process when i tested without the index - doh, what a shame, back to slow magento.

    • Hi Rich,

      I'm not sure why it would be slower, this method is much more reliable.

  • Me

    Hi,

    I've also the following error.

    'Warning: Division by zero in /home/users/goshoftp/go4shopping.nl/fullindex.php on line 11'

    Does anyone know how to solve?

  • Pingback: Dagblastit » Blog Archive » Customizing Magento - Rants from a Web developer, musician, dad()

  • Sean

    Reindex from command line is already built in to Magento 1.4.1.1

    Reindex all

    php -f ./shell/indexer.php reindexall

    show command line switches

    php -f ./shell/indexer.php

  • Claudio

    Hi Ben,
    the script work very well thank you, but for me is not clear this:

    $process = Mage::getModel(’index/process’)->load(7);

    refer to "Catalog Search Index Rebuild Catalog product fulltext search index" (number 7), but the command "load" wath's mean?

    and the command

    $process->reindexAll();

    seems to be refer to the all 8 type of index, included number 7.... it's not clear for me, thanks

    • Claudio

      Hi,
      if I write:
      $process = Mage::getModel(’index/process’)->load(1,8);
      $process->reindexAll();

      the reindex run only to "product attributes" and "stock status" and not for the oder.. it's right? thank

  • Eleventh

    I'm getting this error:

    PHP Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magentodev.catalogsearch_fulltext' doesn't exist' in /var/www/vhosts/domain.com/httpdocs/lib/Zend/Db/Statement/Pdo.php:234
    Stack trace:
    #0 /var/www/vhosts/domain.com/httpdocs/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
    #1 /var/www/vhosts/domain.com/httpdocs/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
    #2 /var/www/vhosts/domain.com/httpdocs/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('truncate catalo...', Array)
    #3 /var/www/vhosts/domain.com/httpdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('truncate catalo...', Array)
    #4 /var/www/vhosts/domain.com/httpdocs/fulltext_indeksavimas.php(8): Varien_Db_Adapter_Pdo_Mysql->query('truncate catalo...')
    #5 {main}
    thrown in /var/www/vhosts/domain.com/httpdocs/lib/Zend/Db/Statement/Pdo.php on line 234

    What's wrong? Problem are in database name, on local.xml are correct data and new server and sql data, but on error I see old server database name, how to change it? Thanks

  • Brilliant! Everything worked effortlessly. I am indexing 7351 products in a few minutes, rather than hours!

    I went into the database with MySQL workbench and deleted the german and french store. For some reason, even though they were disabled, the german store (id3) was still taking a full index. That is in the core_store table. I couldn't get the ssh access, so I just ran the script from the browser - done and done!

    Thank you so much for taking the pain out of full indexing.

  • Thanks for the code Ben!

    Also, if you find you are having troubles with the code, and you copied and pasted it. You should search for any "fancy" quotes and replace those with normal quotes.

  • This is driving me mad! I'm using Magento ver. 1.4.0.1 with the issues mentioned; I ran fulltext.php in the magento root via a SSH, but I'm stuck with this error message and no progress

    Fatal error: Uncaught exception 'Exception' with message 'Warning: Division by zero in /home/amerecom/public_html/fulltext.php on line 9' in /home/amerecom/public_html/app/code/core/Mage/Core/functions.php:245

    Thank you for any help!

    • I'd start by looking at line 9 ...

      • steve

        Heh, fair cop. Perhaps I should turn of my internet when I'm having a stupid day.

        Thanks =)

  • We are having problem, I get error like

    Fatal error: Uncaught exception 'Zend_Db_Adapter_Exception' with message 'SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' in /home/sitename/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php:144

    Stack trace:
    #0 /home/sitename/public_html/lib/Zend/Db/Adapter/Pdo/Mysql.php(96): Zend_Db_Adapter_Pdo_Abstract->_connect()
    #1 /home/ssitename/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(251): Zend_Db_Adapter_Pdo_Mysql->_connect()
    #2 /home/sitename/public_html/lib/Zend/Db/Adapter/Abstract.php(448): Varien_Db_Adapter_Pdo_Mysql->_connect()
    #3 /home/sitename/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SET NAMES utf8', Array)
    #4 /home/sitename/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('SET NAMES utf8', Array)
    #5 /home/sitename/public_html/app/code/core/Mage/Core/Model/Resource/Type/Db/Pdo/Mysql.php(45): Varien_Db_Adapter_Pdo_Mysql->query('SET NAMES utf8')
    #6 /home/sitename/public_html/a in /home/sitename/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php on line 144

    • Look at the error, it can't connect to your database. Check your DB details and try again.

  • Paul

    SSH reindexing took days so we cleared the table mentioned and the lock file directory.

    Started with this script, also took over 16 hours so I stopped that as well.

    We only have ~7500 products, dedicated server with 4GB RAM and timeout settings etc are all set extremely high. However, about 16-22 different stores serving these products. Any idea why it takes this long on our end?

    Status for reindexing always is Processing/Busy, even after truncating the table and emptying lock folder.

    Any idea? Thanks.

  • Paul

    In addition to my last post, we have about 7500 items now in 28 stores.

    According to the calculation on top, does this mean this much?
    7500 (skus) * 0.003 (time p/p) * 28 (store view) * 60 = 37800 seconds

    It still times out eventually on our end. Execution time is set to 0, unlimited.

    Is it an idea to add a storeID soyou can process some individually?

    The widely used "Magento Layered Nav - Badger || Duck" for earlier versions of magento uses such a function as well. He used a function like this in his script, $sx being the store ID:
    ...
    Mage::getSingleton('catalogindex/indexer')->plainReindex($i, null, $sx);
    ...

    Cheers.

    • Its probably not timing out in terms of actual time, but memory.

      You have the equivalent of 210,000 products - using the inbuilt cache rebuild methods will take an exhaustive amount of time.

      We have made a free extension to rebuild the catalog search indexes in a few seconds (for a catalogue of 150k SKUs). Its just getting the time to bundle it and put it on Magento connect.

  • I just wanted to clarify for the "non programmers" using this script. You MUST change

    ~T to "
    and
    ~R to '

    You will notice that if you copy and past it changes some of the " marks to ~T and the ' mark to ~R. I was thinking that the ~T was treated as a " and did not realize it had to be changed.

    Doing this will prevent the following error from happening:

    Parse error: syntax error, unexpected T_STRING in /var/sites/o/xxxx/public_html/fulltext_search_index_cli.php on line 5

  • Tim

    Ben,
    Does your script remove the need to reindex at all in Magento Admin panel? Or does it just do some of the indexes?

    Thanks for the script!

    • Hi Tim,

      No it doesn't. Just read through the post again, and you'll see what/which gets updated.

  • John

    You had mentioned a free extension to rebuild catalogs. Do you have any idea when it will be ready?

  • JD

    Thanks for the great information! It has really helped me out. Now I have another index that's reindexing very slowly(not due to your code or anything here)
    I'm experiencing slow indexing of the "Category Products Indexed category/products association", and wondering if there's a similar table to safely reset like you did with 'truncate catalogsearch_fulltext;'?

    • Hi JD,

      We've not yet come across this index rebuilding particularly slowly (even on 100k+ SKU catalogs). We only tend to "rewrite" code when the core functionality is slowing us down.

      If we do come across it, we'll blog it - if not, you can always get in touch and we can quote to have a look into it for you.

      • JD

        Then it may actually be the 1.4.1 -> 1.4.2 upgrade that has caused my problem. I'll have to do some more searching. Thanks for the help!

        • Hi JD,

          I wanted to follow up on this, I came across it recently on a store with only 11k skus / 4 store views. We'll be looking into a resolution shortly.

  • raner

    Ben,
    By any chance have you come across with any remark or concern about Catalog URL Rewrites being slow, and in fact, showing the issues that the Catalog Search Index has. If so, what kind of solution you guys have that you can recommend to us?

    Thank you so much for you guys are doing for us.

    • Yeah, I'm afraid so. URL rewrites are the second slowest index (at least in 1.4.1) and on large catalogues (10k+) can take quite a while. Its also multiplied by store views if I recall correctly.

      We've been asked to resolve this issue for a client not 1 week ago, as soon as the work has been completed, we'll post the code.

  • Karasik

    Your extension helped a lot! I wish there was some similar solution to fix problems with Index product prices, Index product and categories URL rewrites and Indexed category/products association - these 3 stopped working a week ago and no products are visible on front end (because i truncated tables thinking that rebuilding would happen). I am upset and the store is down - what to do? I am on semi-dedicated server with no ssh access. Please advise.

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

    • werner

      Nice tool thanks
      is it up to date for magento 1.5.0.1

      • Its untested in 1.5.0.1 - but you are more than welcome to try!

        • werner

          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

  • Sumon

    Not stable, before launching software please test properly.

    I am using 1.4.1.1 table not found errors.

    • 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.

  • 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!

    • 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.

  • 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?

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

  • Nik Linders

    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!

  • 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.

  • 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!

  • 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.

  • DVS

    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 ?

    • I'm afraid not.

      No index should take 12 hours to complete unless either it has stalled, or your have 200k+ SKUs.

      • DVS

        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

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

  • 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.

  • Dan

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

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

    • It shouldn't be necessary for 1.5, its for =<1.4

  • mutaaly

    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

  • Pingback: Indexing from command line | Magento Media()

  • hazzy

    The source for fulltext.php is truncated at
    Mage::log( Can you repost it please.

  • Neuro

     we truncated “catalogsearch_fulltext” in phpmyadmin

    now after click “Sonassi Catalogue Search Index”

    there is a success message “Catalog Search Index index was rebuilt”

    but still no products are beeing found in frontend fulltext search. 

  • Pingback: Why You Should Not Use Magento()

  • Thank for post.

    But are and other way...
    so, if you have a lot products .. need change settings for MySQL

    'max_allowed_packet' set ~ 512 M & restart MySQL

    and will work correct

  • Alexander

    Indeed when a large number of goods or attributes need to be saved, backend works very slow, as each time you save data the system re-indexes the goods through the entire catalog.

    Asynchronous Re-indexing is a solution of this problem. When a product or category is saved it is not immediately re-indexed, but put into a queue. The queue is re-indexed in the background. This greatly speeds up the backend. This mechanism is implemented by using an extension of http://mirasvit.com/magento-extensions/magento-asynchronous-reindex.html.

    Even in case you place online shop even on a good hosting, sometimes the products, categories and attributes are saved slowly. This is due to the need to clear the cache and run re-indexing of stored items.

  • Adam Wright

    Does this work for Magento Community 1.7? My catalog_category_flat won't reindex in the Magento admin, but when I reindex via SSH, it looks like it works, but when I back into the admin, it still says "reindex required".

    • sonassi

      @disqus_Q5RT4TRs4o:disqus , this fix is for fulltext_search, not catalog_category_flat.

  • Mike Eckert

    To follow up with the previous questions, will this tool work with version 1.7?