Modern shopsystems, and for the sake of this post I’m counting the dated xt:Commerce 3 as ‘modern’, are full of features. Features you most likely never use. We, for instance, don’t use Tell-a-friend (as it’s not compatible with german law) or wishlists (as we never saw a need for it).

However, chances are good that those unused features turn out to be a bottleneck when it comes to performance. Especially when they are coded by someone who has heard the word ‘MySQL’ for the first time (i.e. those guys who wrote xt:Commerce 3).

At X-Skating we recently faced the problem that the front page loaded pretty good, the page for a single product however had almost one second till time to first byte (the .700 in the screenshot is better than most other tests we ran).

Front page has pretty good TTFB

Product page clocks in at .700, most of the time even worse

Turns out the reason for this is the function getAlsoPurchased() from class product. Figuring out which products other customers also purchased means there is some heavy lifting done on the products_id field (besides others) in table orders_products. However, there is no index set for this field and the query alone takes almost half a second to complete. This is half a second for a feature we don’t use on this site because of poorly coded software! So best is to throw out the code, but if you need this feature and facing the same problem, this will heal your pain:

	ALTER TABLE  `orders_products` ADD INDEX (  `products_id` )

Almost half a second for the query before setting the index…

…and 0.008 seconds afterwards.