OpenCart is slow with many categories

Pssst – There’s an updated version of this post – OpenCart is slow with many categories – 2013 Edition

There is post after post after post on the OpenCart forums regarding OpenCart being slow with many categories. Its quite disappointing with such a promising product.

I only found this out after importing 2,400 categories for a customer. I wrote two scripts to migrate products and categories from a legacy application to OpenCart. I got as far as importing the 2,400 categories and 16 products when the site ground to a halt. Front and back end load times increased to over 20 seconds. I gave up migrating the other 5,000 products, what was the point until I solved this slow issue.

First stop the OpenCart forums, great, loads of posts of people with the same issues. Shame about the replies

  • “You’re doing it wrong”.
    Pretty sure I am not. A stock OpenCart install cannot be “wrong”.
  • “Go use CubeCart”.
    Helpful. I usually do, but I fancied a change.
  • “Why don’t you change to Magento”
    I find it hard to believe forum users are recommending changing products to solve a problem. Why aren’t these posts flagged up!
  • “Buy these two modifications”.
    No. Why should I have to buy some “caching” modifications to make the stock OpenCart work.
  • “Buy my caching modification”.
    No. You say it speeds up OC by 600x? Wow. How does it do that? Minifies JS and CSS, no thanks, I think I’ll get mod_pagespeed to do that for me.
  • “Change your web hosting”.
    I saw this all too often, it is such an easy answer. Forum veterans, copying and pasting and answer and closing the tab.
    The server is quick, it is a VPS, Percona tuned MySQL although it has a stock Apache & PHP stack.

I’m not opposed to buying modifications, but without any real insight into how they work, that $15 is staying in my Paypal account. To me, caching is redis or memcache, not a few lines of PHP.
I can only assume these modifications make use of the built in OC caching API that writes queries to disk. Don’t get me started on caching to disk!

In general I feel the OpenCart community is sadly missing some real server guys, people who know how MySQL works, people who can use memcache and understand speeding things up server side.So I closed the forums and set to work.

Chrome waits 15 seconds for the first byte, its a server issue, no amount of modifications can fix that. Apache OK, PHP OK. I turned on MySQL Slow Query Log and General Log, the issue had to be in MySQL.

Wow, creating a product fires off a LOT of queries

[codesyntax lang=”sql”]
ELECT name, parent_id FROM category c LEFT JOIN category_description cd ON (c.category_id = cd.category_id) WHERE c.category_id = ‘1243’ AND cd.language_id = ‘1’ ORDER BY c.sort_order, cd.name ASC
6 Query SELECT name, parent_id FROM category c LEFT JOIN category_description cd ON (c.category_id = cd.category_id) WHERE c.category_id = ‘1237’ AND cd.language_id = ‘1’ ORDER BY c.sort_order, cd.name ASC
6 Query SELECT name, parent_id FROM category c LEFT JOIN category_description cd ON (c.category_id = cd.category_id) WHERE c.category_id = ’98’ AND cd.language_id = ‘1’ ORDER BY c.sort_order, cd.name ASC
6 Query SELECT name, parent_id FROM category c LEFT JOIN category_description cd ON (c.category_id = cd.category_id) WHERE c.category_id = ’70’ AND cd.language_id = ‘1’ ORDER BY c.sort_order, cd.name ASC
6 Query SELECT name, parent_id FROM category c LEFT JOIN category_description cd ON (c.category_id = cd.category_id) WHERE c.category_id = ’60’ AND cd.language_id = ‘1’ ORDER BY c.sort_order, cd.name ASC
6 Query SELECT * FROM category c LEFT JOIN category_description cd ON (c.category_id = cd.category_id) WHERE c.parent_id = ‘1243’ AND cd.language_id = ‘1’ ORDER BY c.sort_order, cd.name ASC

That is just for one category! (Times that by 2,400 categories)

 

Browsing the front end isn’t much better, it throws a query per category. (Times that by 2,400 categories, each page load)

                   16 Query     SELECT category_id FROM category WHERE parent_id = '2170'

 

Looking at the MySQL indexes OC creates during the install we see the following;

[codesyntax lang=”sql”]
DROP TABLE IF EXISTS `product_attribute`;
CREATE TABLE `oc_product_attribute` (
`product_id` int(11) NOT NULL,
`attribute_id` int(11) NOT NULL,
`language_id` int(11) NOT NULL,
`text` text COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`product_id`,`attribute_id`,`language_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

 

Note the multiple columns on the same index;

[codesyntax lang=”sql”]
PRIMARY KEY (`product_id`,`attribute_id`,`language_id`)

From the MySQL manual

“MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:”

Translation: The above indexes don’t work. MySQL will only set and index and primary key on the product_id column, not the other columns in the create table code.  Searching on partial indexes is slow.

Chris Atomix also discusses this on the OC GitHub site – Multi-column table Indexes causing slowdowns

So how do we fix it?

Until OC uses some sort of nested set model, it is quite easy now we know the issue, we need to alter the tables to have the correct indexes.
To fix the category tables;

[codesyntax lang=”sql”]
ALTER TABLE `category` ADD INDEX `parent_id` (`parent_id`);

ALTER TABLE `product_to_category` ADD INDEX `category_id` (`category_id`);

ALTER TABLE `category_description` ADD INDEX `lanuguage_id` (`language_id`);

ALTER TABLE `category_to_store` ADD INDEX `store_id` (`store_id`);

Also for the product tables

[codesyntax lang=”sql”]
ALTER TABLE `product_attribute` ADD INDEX `attribute_id` (`attribute_id`), ADD INDEX `language_id` (`language_id`);

ALTER TABLE `product` ADD INDEX `manufacturer_id` (`manufacturer_id`);

ALTER TABLE `product_description` ADD INDEX `language_id` (`language_id`);

ALTER TABLE `product_to_store` ADD INDEX `store_id` (`store_id`);

I waiting a minute or so to let MySQL build the new indexes, now page load times are in milliseconds!
So before purchasing any modifications, changing to Magento or changing your web hosting, fix your database.

I hope the OpenCart guys fix this issue soon.

 

Author: Kieran Barnes

Kieran is a PHP developer with 15 years commercial experience. Specialist in WordPress, CakePHP, CubeCart and all things PHP.

54 thoughts on “OpenCart is slow with many categories”

  1. This is really great advise and works like a charm. MYSQL Server was maxing 197% for the two cores just on my test machine and was taking well over 30 secs to return a category. This has now reduced the load times to less than 1 sec. i recommend everyone to adopt this method until OC guys fix the issue.

  2. You really saved my day with your solution! It works great! Loadtime before the changes: 1.9 minutes and now 2 secs!

  3. Thank you for this, helped a lot. Just to add, if you have a lot of nested categories, add index to category_path. Also, if you are using SEO urls, adding index to the url_alias helped a lot as well with the speed.

  4. Intelcom, it is not a file you edit. Re-read the post, you are changing database table indexes.

  5. Hi there, I am planning to have an eshop with 8000-10000products and like opencart. I just read that we are going to have some issues and this post is very very useful!! First of all, I install the opencart and the I run this alteration in phpmyadmin? Is that right?

    Thanks a lot for any answer!!

  6. This is our testing site. – Thousands of products and many categories had the site running at a snails pace (10-15 seconds per product) now we are running at 2 or less per product. AWESOME!!!!! Thank you.

  7. Great tip, also had to index the other fields as mentioned by chachara. Page load reduced from 15-20 secs to 1-2 secs. Brilliant.

  8. I don’t have a great deal of categories but my website takes forever to load or crashes.
    I’m not familiar with editing SQL and wanted to know where I actually have to write your code. ie how do I alter these tables?
    You need to explain it to me like I’m 10 years old 🙂 Thanks

  9. Hello,

    thank you very much, it works really great.
    My site has 10k categories and 25k products, the loading time was 30+ sec, with this mysql mod the loading time is 2-3 secs or less…

    “Great tip, also had to index the other fields as mentioned by chachara.”
    – yes i also had to do this to work, i’m not an expert in mysql but i made these SQL changes:

    ALTER TABLE `ocdemo_category_path` ADD INDEX `path_id` (`path_id`);
    ALTER TABLE `ocdemo_url_alias` ADD INDEX ` url_alias_id` (`url_alias_id`);

    (note that you have to change the table prefix with your own, my was “ocdemo_”)
    maybe it helps to someone.

    thanks again for this 😉

    flerd

  10. Absolutely fantastic. I am running OpenCart 1.5.5.1 with 25K+ products and categories. I saw a performance improvement of about 230%. In addition to chachara recommendations, I also added indexes on the query and keyword columns in the url_alias table. This was a tremendous help. Thank you very much.

  11. Excellent tips. These speeded my site up far more than a pagecache extension I paid nearly $100 for. Agree with you – much better to fix the root causes of a slow site than paper over them by caching. Thanks.

  12. Unfortunately when I mentioned this on GitHub, I was told “i don’t believe this. i think you have misunderstood something.”, even though I pointed directly to the MySQL documentation to back me up.

    The biggest downside to the above solution is that OpenCart will actually remove your indexes when you upgrade. It does a comparison between your table schema and the one in the installation/upgrade SQL file, and any differences (including additional table indexes) will be removed.

    So keep in mind that after every upgrade you’ll need to re-add your indexes, or your site will slow right down again.

  13. Not adding anything new to this thread but just to summarize all the queries that were needed to get this working properly (I had to scroll through multiple times to make sure I got everything so might make it easier for others). Thanks and great post.

    ALTER TABLE `oc_category` ADD INDEX `parent_id` (`parent_id`);

    ALTER TABLE `oc_product_to_category` ADD INDEX `category_id` (`category_id`);

    ALTER TABLE `oc_category_description` ADD INDEX `lanuguage_id` (`language_id`);

    ALTER TABLE `oc_category_to_store` ADD INDEX `store_id` (`store_id`);

    ALTER TABLE `oc_product_attribute` ADD INDEX `attribute_id` (`attribute_id`), ADD INDEX `language_id` (`language_id`);

    ALTER TABLE `oc_product` ADD INDEX `manufacturer_id` (`manufacturer_id`);

    ALTER TABLE `oc_product_description` ADD INDEX `language_id` (`language_id`);

    ALTER TABLE `oc_product_to_store` ADD INDEX `store_id` (`store_id`);

    ALTER TABLE `oc_category_path` ADD INDEX `path_id` (`path_id`);

    ALTER TABLE `oc_url_alias` ADD INDEX ` url_alias_id` (`url_alias_id`);

  14. Hi,

    Thanks for the post. It worked great!

    But, to speed it up even more should all double/triple indexes be deleted?

    Should these be the only indexes in the tables or should there be other ones?

  15. Hans,

    I see no reason why. They won’t slow things down I don’t think.
    Why not test and let us know.

  16. Hello,

    First of all, thank you kleran and rest of you for this magic code.

    I have tested it on a small shop and it does speeds it up.
    So I would like to apply this code to a bigger multi-shop, with many extensions and some modifications..

    Can this optimization make extensions to stop working or have a negative affect on any other OpenCart functionality?

  17. Hi, I’ve run it successfully on stores with 60,000+ products and had no problems. There should be no reason it would produce negative effects.

  18. Glad to hear that, but I’m getting error on first query.
    #1061 – Duplicate key name ‘parent_id’

    Shall I continue applying queries?

  19. Right on, my server was getting crushed by the large amount of products and the categories, I knew it was the queries but never payed close attention to the DB structure. Saved me a ton of time.

  20. This is one miracle code. I have spent hours and hours to figure out the speed issue I had with opencart, I tried all solutions suggest in other forum but this indexing solution is the only thing that came to rescue.
    Thanks a million…I wonder why this is not included in the initial installation as a default..

  21. Hello Guys,

    I’ve been trying to search different forums and to read articles to speed up our website (with back-end in opencart) until I reached this page.
    I’m newbie in opencart and afraid to modified some stuffs as I might crashed our website. Anyway, can you post here the step by step procedures you did to speed-up your OpenCart. I’ve checked the databases and all the added indexes were present already. Please advice. @_@

  22. If you are not technical, I can offer an OpenCart tuning service. Please contact me direct to discuss.

  23. Hi Kieran, what’s your email? I am a technical person so maybe I can work it on my own but the problem is I don’t have any idea how to tweak the website.

  24. I get this error when I tried to execute the code:

    ERROR 1061 (42000): Duplicate key name ‘parent_id’
    ERROR 1061 (42000): Duplicate key name ‘category_id’

    Does this mean that the index is already set?

  25. Your turbo script made a huge difference to the page load times on an opencart 1.5.6 install with 720 categories and 6000 products. Thank you so much. Page loads went from 8 – 20 secs down to 3 secs per page.
    Thanks again, excellent work.

  26. To answer folks above, yes if you are getting error 1061 it means a it already has key defined for that col.

    Confirmed, out SQL is running all day 60-90% cpu cause of OC. Tried it out on 3 test stores, 1 of them was stock with only stock inventory, all of them are 1.5.6 got faster. The real store is an OC install with like 8 multistores, 8k products, 500 categories, creepingly slow. Like 5-8 seconds. Ran queries, live store turned into normal request speed for our VPS.

    Note, there is a script on git turbo.php that tries to do this automatically to all tables, using _id as a trigger col. Tested that too, seems to work fine and dandy in big test store although seems a bit overkill the way it indexes. Better to manually make a list of your queries and just paste them in phpmyadmin (or sqlbuddy if you use openshop).

    Thanks guys

  27. @haupin:

    Oiiiih, that DB-Look makes a hell of a difference, compared to the default One, this is the first (SQL) DB-Style, I’ve seen so far, where even a NO-Programmer can read Data in an easy, clean and nice form. Not ‘2000+’ >>INSERT INTO…<< repetitions, mostly just data, just from looking at it, it MUST work much faster…

    I tried it already, but I cannot tell the difference yet, since I only have 4 Products to show as well as a fast 'place' already. I came here to ask, is this fundamental 'DB-Rebuild' would have any effect on any existing Mod's, directly accessing the DB to add/use Values, during install or then if actively in use. If so, it would be interesting to find out, what should NEVER be done, when your great Contribution is active in use.

    Thank you very much

    with my best regards,

    Ernie
    more or less SQL-DB Newbie, still used to (indexed!) flat-file DB's…!
    Was (still) much faster than this thing here ever will be…

    ipc.li/shop/

  28. Hello guys! I tried the above alterations to the db (about 3500 products), but unfortunately I don’t see any performance improvement… 🙁
    I even added to the index the query and keyword columns in the url_alias table, as mentioned by John.

    Should I try something else? Any ideas?

  29. Thanks – the sorted a 10+ second page load time down to 1 second on a site with 2000 products and 150 categories.

  30. Kieran Barnes and Munjal Subodh,
    You are the great!
    In OC 1.5.6.4; reduced from 10 sec to 2 sec. I have 420 categories and 9600 products

  31. Thanks.

    This really helped a site I’m building which has nearly 4000 categories, its much much quicker now. (albeit I’ve already changed some code in the category controller to limit the number of times the db gets called)

  32. This “tip” with mysql indexes works in the opencart 2 version ??

    Anyone tested?

  33. Thanks – it seems to make a big different, for a friends old shop on OpenCart 1.4.9

    On backend products are loading now much faster!

Leave a Reply

Your email address will not be published. Required fields are marked *