One of my clients has a rather large shop, with in excess of 40,000 products over a couple of thousand multi-tiered categories. Recently the company’s website started to run slowly, seemingly at random times of the day. The reason was surprising.
After some frustrating investigation, the client realised that after each period of high server load he’d often immediately receive an email confirmation of an order being placed on the website.
The order confirmation script, (in this case a HSBC ePDQ callback), queries the database for an order matching a UID passed from HSBC. The query also checks that the order is still marked as un-paid, as added protection against duplicate orders.
SELECT * FROM `orders` WHERE `uid` = '$orderIdFromHSBC' AND `paid` = 0 LIMIT 1
There was no index on `uid` or on `paid` and there were heaps of orders in the table… this customer in particular doesn’t like to archive his old orders.
The addition of a single index on both columns, in order of reference, (it’s more efficient, don’chaknow), fixed the problem immediately.