alternative indexing solution , is there any suggestions?

Question

the original magento indexing happened to be so slow , in my case I had millions of SKUs need to be indexed , converted to flat table & url rewritten .

I am not talking about search indexing .

original magento indexing takes to many days & then it stop

after digging in the code I saw its very complicated task and usually it delete all the catalog_product_flat table causing the site to go offline

any suggestion to have custom indexer , faster indexer ???

Magento Version : CE

thanks for suggestion

to be clear :

I am importing my product using custom api , but these product need to be indexed ( in magento way ) to be added to url rewrite table , converted to flat .. etc , these are magento specific tasks and they are so slow

Answer 1

There is a point at which the flat catalog itself may become more of a performance bottleneck than a performance benefit due to the time required to reindex. This limit is variable and may be subjective based on your business.

I have seen the flat catalog reindexer churn miserably on stores with just 100k products; but have seen it work well on stores with 300-400k products. A lot has to do with hardware and production traffic volumes; number of store views and stores play into this quite a bit as well.

Because it varies from store to store based on your hardware and the specific distribution of your catalog it would be hard to make a blanket recommendation about how to "fix" the indexer. It may be advisable to split your database off, separate read/writes into a master/slave and beef up your hardware. If that's not an option you can try your hand at benchmarking against the EAV catalog (disable flats).

Additionally a primed full page cache may limit the 'disappearance' of items on the frontend during reindex.

However, if you're at the point of attempting to reindex MILLIONS of products you've likely exceeded what is possible on your hardware and, I hate to say it, what is prudent to run on CE. Substantial indexer improvements have been made to EE as of 1.13+ and you may want to do some research to find out if your store's 'downtime' from indexing woes equate to substantial loss / opportunity cost which would justify the expense.

Answer 2

You are in no-mans land. EE is too expensive at $15,000,yr, you need to use an upload tool such as Magmi for volume updates, your hosting is inadequate, version 1.8 is not production ready.

Either you use standard API with auto-indexing and delta loads (only changed records), move to EE, or use temporary scalable hosting. With millions of products you expect to generate $millions revenue per year, which would mean it smart to use EE, therefore likely the site is new not generating any revenue. The last option is to reduce your product count until you generate enough revenue to afford EE ($3million/yr+ revenue) and/or a scalable burstable hosting platform.

Answer 3

When you reach a huge number of document, you should switch to a specialized search engine solution. They are build to handle millions of document efficiently.

I would suggest to have a look at the existing Magento extensions based on real search technology (Sphinx, Solr):

http://www.magentocommerce.com/magento-connect/catalogsearch/result/?id=&s=1&pl=0&te=0&q=solr&t=0&p=1

Answer 4

Unless you are a programmer with a lot Magento and DB experience, I would not try to modify the way the indexers work.

Your best bet would be to upgrade to the latest release, 1.8 CE. The core team at Magento has reworked the way that indexing is done to better support large (and in your case very large) product catalogs. Since every Magento site has unique needs, I can not guarantee that this will solve your problem, but we have seen some good improvements in speed on the latest version.