MySQL command Explain ignore LIMIT?

Question

I use MySQL server version 5.5.14 and now I am trying this simple SQL query with Explain command:

EXPLAIN SELECT id, name, thumb FROM `twitter_profiles` LIMIT 10;

and it shows me this result:

+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | tp    | ALL  | NULL          | NULL | NULL    | NULL | 40823 |       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.02 sec)

My question is why it scans whole table instead of taking the first 10 rows as I specified in LIMIT clause?

Katakana

Answer 1

here a good link of article about MySQL EXPLAIN limits and errors

LIMIT is not taken into account while estimating number of rows Even if you have LIMIT which restricts how many rows will be examined MySQL will still print full number

Oeflein

@Evgi Aha, but it is only about estimation process, right? In face, mysql will take only mentioned 10 rows, is it true? – Katakana

@Jakub, unless you add an ORDER BY clause, which is usual case (otherwise, your 10 rows are neither random nor deterministic). It needs to retrieve all matching rows in order to sort them, even if they are eventually not sent to the client. – Goner

@Alvaro Yes, it seems that I have to add order by clause with reference to indexed column name and explain give me what I am expecting = 10 rows. So it is right solution for this cause? – Katakana

If you add an ORDER BY you change the whole execution plan, so EXPLAIN will give you a different result depending on the index used for sorting (if any). – Hitoshi

Aha, so I dont be worried about this, because in fact mysql will scan only 10 rows from LIMIT clause? It is only EXPLAIN issue? – Katakana

Answer 2

You need to use order by:

EXPLAIN SELECT id, name, thumb
FROM twitter_profiles ORDER BY LIMIT 10;

Sincere

This is not even valid SQL. – Goner