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?

Thank you for your advice in advance!

Cheers,

Jakub

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

Answer 2

you need to use order by..

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

Hope this will help you out.