Once in a while I actually get to remember enough to help someone - with something I’m actually interested in - not often, but just sometimes. 🙂 Generally I do as much learning as helping along the way.
Someone asked about a slow MySQL query on #sage-au tonight. They had been asked to look into a small e-commerce site that’s been having issues for a few years, slow page loads and the like.
It’s always fun working blind (no access to schema or the server), but if there’s anything K-T has taught me it’s that nothing is impossible to solve by asking questions.
The thing with SQL queries is that the system can do weird and wonderful things, especially when you’re doing subqueries and the like.
The query was as follows:
Fairly simple, delete something if it’s not in a list of vendor id’s which are related to product orders (ish?)
The first things suggested were to throw more hardware at it, which had most definitely been done - for a small online store it had 8GB of ram and a quad core processor of some sort.
Running the selects by themselves were fast (alone or in the sub-query state), and nothing major seemed out of the ordinary as shown by the output of explain.
For ~9k orders, there’s ~9k rows, that makes sense.
There were triggers which could cause other horrible things to happen with deletes. Profiling wasn’t turned on (yet) so that wasn’t slowing it down. No stored procedures either.
The fun part came when profiling was turned on. Long story short, it was chugging through the database thousands of times over:
SlowDB: 2910567 rows in set (12.87 sec) before i ctrl+c
Well, that’s no right! ~9k orders, ~170 vendors. 3 million rows queried? Silly MySQL. Ok, silly schema, but you get the idea.
Something I should probably have asked about were indexes. A lack of indexing’s a sure-fire way to slow down any queries, especially if there’s subselects and joins and all sorts of other horrible things going on.
It seems there wasn’t an index on the oid column in vendor_shipping. This meant that every time it filtered the query (with a where) for something in the vendor_shipping table it would have to scan through it row by row. The subselects only made this exponentially worse!
I’ll leave it to the code to show the difference - remember that 17 seconds was the normal time for this query to run.
We double checked our assumptions and confirmed that index creation load shouldn’t be too high. 167 rows, a single INT column, and it was already slow - should be OK. (This is something you have to think about when messing with FULLTEXT indexes.)
Here the index on the table is created:
That was quick!
And now, to test running the DELETE command:
Suffice to say, the issue was closed. 🙂
The original chat log is below, mainly so I can remember how it went. Other people’s names have been changed, but the content’s the same. 🙂