Hello Magento Wizards of t'Internet,
I hope someone out there can help. I have a structure of categories that I am about to shuffle about. Essentially it involves moving third-tier categories into a newly-created top-level category.
There are up to 100 products in each category.
Whenever I try to drag & drop the categories I get the 'Please wait…' notice but it takes so long – I'm talking several minutes – for something to happen.
- Sometimes the 'Please wait' notice stays on the screen permanently
- Sometimes the category does move
- Sometimes I get the error message; "Category move error exception 'PDOException' with message 'SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction' in /home/myuser/public_html/mydomain/lib/Zend/Db/Statement/Pdo.php:228…"
There's no difference whether I'm moving a category with 300 products or 3 products.
I've just turned the Index mode to Manual Update to see if it was the saving action eating up the time but it seemingly hasn't made any difference.
This is rather irritating because I currently have categories scattered around – not good for potential customers.
Are there any tips or know-how on how to speed this process up? I am using a Simple Servers (UK) as I am in the UK, and it's supposed to be a Magento-optimized server.
Update: I've noticed that the Catalog URL Rewrites index is almost always stuck on Processing. Could this be a cause/effect of my issues?
Update #2: Okay, delving deeper I can see that in my URL Rewrite Management I have a LOT of entries. Considering we have about 400 products, we have over 1.7 million entries. Wowsers. I'm assuming this is a result of changing category structures over the years. So, maybe it's time to clean this mess up! Does the Truncate core_url_rewrite table method actually work safely?
Update #3: Okay, I feel I should keep this updated in case anyone experiences the same issue. This should be the last update because I've figured out why the core_url_rewrite table had so many entries. Essentially we had duplicate URL Keys in our products. Not many, but enough to create problems. Add onto that the fact that a cron job refreshed the Indexes every 5 minutes and you're talking 50,000 new entries in the URL Rewrite table every day!
Short-term solution: I truncated the core_url_rewrite table.
Long-term solution: I will be locating every duplicate URL Key and making them unique. This should stop new rewrites being created every time I index the site. My site is instantly faster front and backend. In terms of SEO issues, well time will tell. But this is a learning curve for the future.
Best Answer
The category move is an expensive event.
It triggers the reindexing of url rewrites and category product association (and maybe others).
You should put the indexes on 'manual' instead of "on save", move your categories around and reindex everything when you are done.
I'm not sure that truncating
core_url_rewritetable will solve your problem.It may solve it for your first category move, but you will end up with a lot of rewrites again.