Why does a MySQL query take anywhere from 1 millisecond to 7 seconds?
mysql, sql
Solution
To specifically answer your question: it's not a specific part of the query that's causing the wide range of performance. That's MySQL doing what it's supposed to do - being a Relational Database Management System (RDBMS), not just a dumb SQL wrapper around comma separated files.
When you execute a query, the following things happen:
- The query is compiled to a 'parametrized' query, eliminating all variables down to the pure structural SQL.
- The compilation cache is checked to find whether a recent usable execution plan is found for the query.
- The query is compiled into an execution plan if needed (this is what the 'EXPLAIN' shows)
- For each execution plan element, the memory caches are checked whether they contain fresh and usable data, otherwise the intermediate data is assembled from master table data.
- The final result is assembled by putting all the intermediate data together.
What you are seeing is that when the query costs 0.0009 seconds, the cache was fresh enough to supply all data together, and when it peaks at 7.5 seconds either something was changed in the queried tables, or other queries 'pushed' the in-memory cache data out, or the DBMS has other reasons to suspect it needs to recompile the query or fetch all data again. Probably some of the other variations have to do with used indexes still being cached freshly enough in memory or not.
Concluding this, the query is ridiculously slow, you're just sometimes lucky that caching makes it appear fast.
To solve this, I'd recommend looking into 2 things:
- First and foremost - a query this size should not have a single line in its execution plan reading "No possible keys". Research how indexes work, make sure you realize the impact of MySQL's limitation of using a single index per joined table, and tweak your database so that each line of the plan has an entry under 'key'.
- Secondly, review the query in itself. DBMS's are at their fastest when all they have to do is combine raw data. Using programmatic elements like `CASE` and `COALESCE` are by all means often useful, but they do force the database to evaluate more things at runtime than just take raw table data. Try to eliminate such statements, or move them to the business logic as post-processing with the retrieved data.
Finally, never forget that MySQL is actually a rather stupid DBMS. It is optimized for performance in simple data fetching queries such as most websites require. As such it is much faster than SQL Server and Oracle for most generic problems. Once you start complicating things with functions, cases, huge join or matching conditions etc., the competitors are frequently much better optimized, and have better optimization in their query compilers. As such, when MySQL starts becoming slow in a specific query, consider splitting it up in 2 or more smaller queries just so it doesn't become confused, and do some postprocessing in PHP or whatever language you are calling with. I've seen many cases where this increased performance a LOT, just by not confusing MySQL, especially in cases where subqueries were involved (as in your case). Especially the fact that your subquery is a derived table, and not just a subquery, is known to complicate stuff for MySQL beyond what it can cope with.
Problem
I have an SQL query(see below) that returns exactly what I need but when ran through phpMyAdmin takes anywhere from 0.0009 seconds to 0.1149 seconds and occasionally all the way up to 7.4983 seconds. Query: ``` SELECT e.id, e.title, e.special_flag, CASE WHEN a.date >= '2013-03-29' THEN a.date ELSE '9999-99-99' END as date CASE WHEN a.date >= '2013-03-29' THEN a.time ELSE '99-99-99' END as time, cat.lastname, FROM e_table as e LEFT JOIN a_table as a ON (a.e_id=e.id) LEFT JOIN c_table as c ON (e.c_id=c.id) LEFT JOIN cat_table as cat ON (cat.id=e.cat_id) LEFT JOIN m_table as m ON (cat.name=m.name AND cat.lastname=m.lastname) JOIN ( SELECT DISTINCT innere.id FROM e_table as innere LEFT JOIN a_table as innera ON (innera.e_id=innere.id AND innera.date >= '2013-03-29') LEFT JOIN c_table as innerc ON (innere.c_id=innerc.id) WHERE ( ( innera.date >= '2013-03-29' AND innera.flag_two=1 ) OR innere.special_flag=1 ) AND innere.flag_three=1 AND innere.flag_four=1 ORDER BY COALESCE(innera.date, '9999-99-99') ASC, innera.time ASC, innere.id DESC LIMIT 0, 10 ) AS elist ON (e.id=elist.id) WHERE (a.flag_two=1 OR e.special_flag) AND e.flag_three=1 AND e.flag_four=1 ORDER BY a.date ASC, a.time ASC, e.id DESC ``` Explain Plan: The question is: Which part of this query could be causing the wide range of difference in performance?