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?

score 16 · Accepted Answer · answered Jul 21 '11 at 11:50

16

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

answered Jul 21 '11 at 11:50

Haim Evgi

123,187
45
217
223

@Evgi Aha, but it is only about estimation process, right? In face, mysql will take only mentioned 10 rows, is it true? – Jakub Mach Jul 21 '11 at 11:53
1

@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. – Álvaro González Jul 21 '11 at 11:58
@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? – Jakub Mach Jul 21 '11 at 12:25
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). – arnep Jul 21 '11 at 13:06
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? – Jakub Mach Jul 21 '11 at 13:12

score -4 · Answer 2 · edited Apr 05 '20 at 15:23

-4

You need to use order by:

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

edited Apr 05 '20 at 15:23

halfer

19,824
17
99
186

answered Jul 21 '11 at 11:52

iCoder4777

1,682
1
14
35

8

This is not even valid SQL. – Álvaro González Jul 21 '11 at 12:00

MySQL command Explain ignore LIMIT?

2 Answers2

Linked