Magento Reindexing problem - take too long time


There are problem in reindexing in magento many times due to number of factors:

  • The number of products
  • The number of store views
  • The apache/lighttpd timeout setting
  • The php.ini maximum execution time & script input time
  • The lock file is still place from the previous time can preventing it running again
one more thing VPS or dedicated server is must have for Magento.

here i paste the trick/tips for the same.

First of all increase your execution time value from the .htaccess
 # php_value memory_limit 64M  
   php_value memory_limit 128M  
   php_value max_execution_time 18000  
Now open your shell/terminal:
go on upto "public_html/app/shell"

use "ls -l" command, to list out the files.
there is one indexer.php file in the list.

Commads:
  php indexer.php --info (Will shows list of allowed indexers)   
  php indexer.php --reindex all (Reindex data by all indexers)  
If you wish to reindex one by one then use following commands:
 php indexer.php --reindex catalog_product_attribute (Reindex Product Attribute)   
  php indexer.php --reindex catalog_product_price (Reindex Products prices)   
and so on.....

If you don't have SSH (Command Prompt) access then try with following script;
 <?php  
 require_once 'app/Mage.php';  
 $app = Mage::app('admin');  
 umask(0);  
 for ($index = 1; $index <= 8; $index++) {  
     $process = Mage::getModel('index/process')->load($index);  
     $process->reindexAll();  
 }  
Save file with reindex/php and run go on browser and run it like: http://yourdomainname/reindex.php

Hope this helps,
Do you have any tips? Post them in the comments and I'll add them to the list!

Cheers :)

Comments

  1. Thanks for the step by step for indexing via command line, but how do you check the status of the indexer to see if it's still running, etc. ?

    ReplyDelete
  2. Usage: php -f indexer.php -- [options]

    --status Show Indexer(s) Status
    --mode Show Indexer(s) Index Mode
    --mode-realtime Set index mode type "Update on Save"
    --mode-manual Set index mode type "Manual Update"
    --reindex Reindex Data
    info Show allowed indexers
    reindexall Reindex Data by all indexers
    help This help

    Comma separated indexer codes or value "all" for all indexers

    ReplyDelete
  3. Thanks Vern. It's weird no matter how I run the command to reindex individual indexes, indexer.php will run them all. I have tried:

    --reindex catalog_product_attribute
    --reindex catalog_product_attributes
    --reindex product_attributes
    --reindex product_attribute
    --reindex 1

    and also --[space]reindex

    All commands will lead to reindex all..

    We also have 10 store views and reindex "Category Products" is taking over 10 hours so far... (still running) lol

    ReplyDelete
  4. Having many store views will make the reindex take forever. One thing that helps is to NOT use flat_products or flat_categories (this is set in the admin).

    ReplyDelete
    Replies
    1. @Greg
      When you have a large database with products in Your shop, DO NOT disable the flat products and categories structure, because this does speed up by a factor of 2 the display of your products !!!

      Delete
  5. hanks Vern for your input :)
    @Greg: Well, EAV database structure for categories, products. In some cases this solution is not the best or fastest..
    Well, Both EAV and Flat have their own advantages..

    I have one script for re-index; that you can run direct from browser (if you don't have SSH access then :)
    I edited into post

    ReplyDelete
  6. Someone know if its possible to run the re-indexation by store view? i have 80 store views and 10K + products just to run the catalog_category_product takes more than 6 hours with the shops closed :s

    ReplyDelete
  7. Thanks for this article. I'm new to Magento and the re-indexing is driving me crazy. I'm making a lot of changes to my (imported) catalog at the moment and I just want to be able to see how that looks, but then I have to re-index (for days) before the stuff shows up on the front end. In the meantime I have broken links.

    When you re-index, can you continue to use the admin area and the site? Or do you have to have to stay out of it?

    ReplyDelete
    Replies
    1. when you do re-index, actually, it will re index the data's, so it may possible as you told, (link broken) on frontend and on backend it gives error of processing, keep practice to avoid to access such menu's which you put for reindex (like catalog).

      Delete
  8. When I am executing the indexer command using ssh this talk long time and never show successfully message after 2nd one index.

    It shows success message for 1st 2 index.

    then its never show same message after that...

    please help me out.

    ReplyDelete
  9. 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.

    ReplyDelete
  10. Which Magento Version I must choose. I tried to Opencart and Zencart also, Magento take too time to Hosting filezilla.

    http://pschunt.blogspot,in

    ReplyDelete
    Replies
    1. Of course latest one..! btw, why don't you used SVN to host the magento. I guess, you have access of SSH.
      http://www.magentocommerce.com/svn

      Delete
  11. Hello i have the serious issue pls solve it my problem is catalog url rewrite mode still displaying processing pls help me

    ReplyDelete
    Replies
    1. How you attempt reindex process? i mean by browser or SSH? however, please reindex it one by one instead of doing it together.

      Also, how many products are there in your store???

      Delete
  12. Hi. this drove me absolutely mad trying to run this through magento backend. I now use Putty and do it through SSH. It takes me 5 minutes now. I log on via putty and use the following commands:

    cd public_html

    THEN press enter

    THEN

    php shell/indexer.php reindex all

    After that it should index all data very quickly.

    Good luck as I know how frustrating it is...

    ReplyDelete
    Replies
    1. I'm glad this article solution works for you.. Cheers :)

      Delete
  13. Hi Gaurav,

    Thanks for sharing this information . keep on posting this kind of posts.


    Magento Developers

    ReplyDelete
    Replies
    1. Hey kamal, I'm glad that this information helps you.. Cheers!

      Delete
  14. This comment has been removed by the author.

    ReplyDelete
  15. Hey Guys,
    We just published an extension that addresses reindexer timouts (through the browser) by utilizing the Magento Cron job. Come take a look! http://www.magentocommerce.com/magento-connect/catalog/product/view/id/18404/

    ReplyDelete
  16. Hey thanks for sharing this trick. I would wish you to have a look at this post as well which will be helpful http://magedeveloper.wordpress.com/2012/08/16/magento-reindexing-problem-take-too-long-time/ Thanks for sharing this. I just recently tried out this issue when I have been installing Magento product reviews extension and then I found this very helpful and rectified.

    ReplyDelete
  17. Hii Please help me i am facing problem with product attribute reindexing process.if you have any solution then please post here.its very urgent for me.

    thnx in adnavce.

    ReplyDelete
    Replies
    1. Above blog post and comments are as solution. Please paste in detail about your issue. Thanks

      Delete
  18. Having a lot of problems with trying to index product flat data. I've trawled the forum for hours trying the various methods that have worked for others. I tried your non ssh method but came up with this.
    load($index);
    $process->reindexAll();
    }
    ?>

    Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`jasminew_marose/#sql-4a89_1c0f5`, CONSTRAINT `FK_MG_MG_CAT_PRD_FLAT_1_ENTT_ID_MG_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `mg_catalog_product_entity` (`entity_id`) ON DELETE)' in /home/jasminew/public_html/marandr/lib/Zend/Db/Statement/Pdo.php:228 Stack trace: #0 /home/jasminew/public_html/marandr/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array) #1 /home/jasminew/public_html/marandr/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) #2 /home/jasminew/public_html/marandr/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array) #3 /home/jasminew/public_html/marandr/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) #4 /home/jasminew/public_html/marandr/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABL in /home/jasminew/public_html/marandr/lib/Zend/Db/Statement/Pdo.php on line 228

    Has anyone got any ideas. I've also tried the reindexall method via a cron job, but I can't tell if it ran successfully or not - just get an email to say it ran but I can't find anywhere to say that it was succesful or otherwise. I'm starting to round in circles with this. Ver.1.7.0.2

    ReplyDelete
    Replies
    1. Above error is because of foreign key constraint. There is relations between two tables and due to that it not allow to add/update row if that record not exit on master table. Set foreign key check constraint null by using below command and try to execute again.
      SET foreign_key_checks = 0;

      Hope it helps you, good luck!

      Delete
  19. Please ignore "load($index);
    $process->reindexAll();
    }
    ?>"
    It was supposed to paste the php code with the error but obviously failed

    ReplyDelete
  20. hi manpreet
    use can use that code then slove trhat problem
    you can remove ALTER TABLE `catalog_category_product_index`
    ADD CONSTRAINT `FK_CATALOG_CATEGORY_PROD_IDX_CATEGORY_ENTITY` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `FK_CATALOG_CATEGORY_PROD_IDX_PROD_ENTITY` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;”

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. I tried to reindex using the reindex.php way as i am a bit confused as how to it wil the command promt but when i ran the file through my browser i get the same message as i do when i run it through the back end

    "Request Timeout"

    "This request takes too long to process, it is timed out by the server. If it should not be timed out, please contact administrator of this web site to increase 'Connection Timeout'."

    Please help as nothing is being returned in my store when i search for the product names or SKU codes. This is very fustrating as my store is meant to go live by the end of this week.

    Thank you

    ReplyDelete
  23. How to solve Reindexing problem in magento
    Use three step in magento database (mysql) and Solve Re-indexing Problem

    // First step Start ///////

    DROP TABLE IF EXISTS `index_process_event`;
    DROP TABLE IF EXISTS `index_event`;
    DROP TABLE IF EXISTS `index_process`;

    //first step Complete///////


    // Second step Start /////

    CREATE TABLE `index_event` (
    `event_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(64) NOT NULL,
    `entity` VARCHAR(64) NOT NULL,
    `entity_pk` BIGINT(20) DEFAULT NULL,
    `created_at` DATETIME NOT NULL,
    `old_data` MEDIUMTEXT,
    `new_data` MEDIUMTEXT,
    PRIMARY KEY (`event_id`),
    UNIQUE KEY `IDX_UNIQUE_EVENT` (`type`,`entity`,`entity_pk`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;

    CREATE TABLE `index_process` (
    `process_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `indexer_code` VARCHAR(32) NOT NULL,
    `status` ENUM('pending','working','require_reindex') NOT NULL DEFAULT 'pending',
    `started_at` DATETIME DEFAULT NULL,
    `ended_at` DATETIME DEFAULT NULL,
    `mode` ENUM('real_time','manual') NOT NULL DEFAULT 'real_time',
    PRIMARY KEY (`process_id`),
    UNIQUE KEY `IDX_CODE` (`indexer_code`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;

    ///second step complete//////////////////


    // Third step complete complete //////

    INSERT INTO `index_process`(`process_id`,`indexer_code`,`status`,`started_at`,`ended_at`,`mode`) VALUES (1,'catalog_product_attribute','pending','2010-02-13 00:00:00','2010-02-13 00:00:00','real_time'),(2,'catalog_product_price','pending','2010-02-13 00:00:00','2010-02-13 00:00:00','real_time'),(3,'catalog_url','pending','2010-02-13 19:12:15','2010-02-13 19:12:15','real_time'),(4,'catalog_product_flat','pending','2010-02-13 00:00:00','2010-02-13 00:00:00','real_time'),(5,'catalog_category_flat','pending','2010-02-13 00:00:00','2010-02-13 00:00:00','real_time'),(6,'catalog_category_product','pending','2010-02-13 00:00:00','2010-02-13 00:00:00','real_time'),(7,'catalogsearch_fulltext','pending','2010-02-13 00:00:00','2010-02-13 00:00:00','real_time'),(8,'cataloginventory_stock','pending','2010-02-13 00:00:00','2010-02-13 00:00:00','real_time');

    CREATE TABLE `index_process_event` (
    `process_id` INT(10) UNSIGNED NOT NULL,
    `event_id` BIGINT(20) UNSIGNED NOT NULL,
    `status` ENUM('new','working','done','error') NOT NULL DEFAULT 'new',
    PRIMARY KEY (`process_id`,`event_id`),
    KEY `FK_INDEX_EVNT_PROCESS` (`event_id`),
    CONSTRAINT `FK_INDEX_EVNT_PROCESS` FOREIGN KEY (`event_id`) REFERENCES `index_event` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_INDEX_PROCESS_EVENT` FOREIGN KEY (`process_id`) REFERENCES `index_process` (`process_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;

    ///Third step complete complete //////////////////

    ReplyDelete
  24. This comment has been removed by the author.

    ReplyDelete
  25. SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: UPDATE `catalog_product_entity` SET `entity_type_id` = ?, `attribute_set_id` = ?, `type_id` = ?, `sku` = ?, `has_options` = ?, `required_options` = ?, `created_at` = '2016-08-17 07:25:06', `updated_at` = '2016-08-16 13:18:54' WHERE (`entity_id`=6)

    i am product edit after get this error plz give me answer

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete
  27. I think this article will fully complement you article. Please continue publishing helpful topics like this.
    web design company in chennai
    web development company in chennai

    ReplyDelete

Post a Comment

Popular posts from this blog

Magento Navigation menu of CMS pages

Wishlist with wished features - Add items into wishlist with options and allow to add certain amount of items into wishlist box.