I am running a query:

SELECT * FROM message where fr_user = 1 ORDER BY created DESC LIMIT 10;

Without the LIMIT there would be 170 rows returned where fr_user = 1;

I am now trying to understand how efficient this query (not trying to improve the query, just to see how efficient it CURRENTLY is) is.

I have ran the query and looked at the execution plan:

  • In the execution plan there is a query cost of 43.00... what does this mean/how is it calculated?

  • What do the four fields (Read, Eval, Prefix, DataRead) mean?

  • Why does the ratio of rows examined to the ratio of rows produced = 100% when there are 170 rows examined and 10 rows produced? I have an index (fr_user, created) so why are all the rows (170) being examined? If I have to examine all the rows it would seem pointless to have an index then?

enter image description here

  • Can mysql read an index partially backwards in this condition? Does ORDER BY fr_user DESC, created DESC change the query plan? – Michael - sqlbot Feb 14 '16 at 1:47

(To answer @Michael, then segue into the OP's question.) ORDER BY fr_user DESC, created DESC can use an index backwards if all are DESC. Or, as in this orginal case, it can do WHERE fr_user = constant ORDER BY created DESC when you have INDEX(fr_user, created). It simply goes to the end of the fr_user=1 entries in the index and walks backward.

But your question is "why are all 170 rows examined". Well, they aren't. The "170" is bogus because EXPLAIN almost always ignores LIMIT. This is a known failing of EXPLAIN. The Optimizer, in some situations, does take LIMIT into consideration.

  • Thanks for the reply. So does that mean only 10 rows are really being examined in my case if the ratio is 100%? – user2924127 Feb 14 '16 at 19:11
  • Yeah, can't trust the 100%, either. – Rick James Feb 15 '16 at 4:04
  • Take a look at EXPLAIN FORMAT=JSON SELECT .... (If you are running a new enough version of MySQL.) – Rick James Feb 15 '16 at 4:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.