Mysql core_url_rewrite product sorting

[rimoutas]

My database process list for a long time keeps sorting products in core_url_rewrite table. And it still going on. Where could be the problem and how could I solve the problem? These sortings slows down my site.

Thanks for you efforts helping me

[robfico]

What version of Magento are you running? How many rows are in your core_url_rewrite table? How many products do you have in your store?

[rimoutas]

Magento version is 1.9.0.1

core_url_rewrite table has about 800 000 rows

There are 21 556 products and 526 categories in the site with 7 stores.

[Theis Corfixen]

Hi @rimoutas 

Unless you are un a super tiny setup server wise you should be able to handle this, there are a ton of guides outthere on how to tweak MySQL performance to get the most out of Magento I would suggest looking one of those up, and try ajusting your setup to that.

[robfico]

Although 800,000 rewrites is quite a bit for only 22k products, it shouldn't bring performance down during a reindex. It sounds like either your web host is not optimized for Magento, you're on a server / VPS that is overloaded by other clients, or your hosting instance has insufficient cpu/memory resources available.

I'd start with the hosting environment to see if you can remedy the performance issues. Additionally, you could look at why you have so many rewrites. Maybe from the 1.7/1.8 version bug that caused extra rewrites, or your uploads not having unique keys, or a plugin causing this to happen.

[rimoutas]

Thank you for all the answers, but server is not a problem. There are professionals who working with this server. I bet there are so many rewrites because sometimes url key changes and magento automatically add a rewrite and redirection for that which is good. Server professionals told me, that the problem is with order by. It supposed to be removed.

As I noticed when a bunch of sorting results finished, then other sorting results comes. And sorting keeps going.