Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL command Explain ignore LIMIT?

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?

like image 525
Jakub Mach Avatar asked Jul 21 '11 11:07

Jakub Mach


People also ask

What is LIMIT command in MySQL?

In MySQL the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments which are offset and count. The value of both the parameters can be zero or positive integers.

What does LIMIT N 1 1 mean in SQL?

SELECT column_list FROM table_name ORDER BY expression LIMIT n-1, 1; In this syntax, the LIMIT n-1, 1 clause returns 1 row that starts at the row n. For example, the following query returns the employee information who has the second-highest income: SELECT emp_name, city, income FROM employees.

How do I remove a LIMIT in SQL query?

You can toggle the LIMIT clause added by MySQL Workbench via the SQL Editor tab within the application preferences (Edit menu -> Preferences...). Simply un-check the "Limit Rows" option within the Query Results section as pictured below.

How do I LIMIT data in MySQL?

Assume we wish to select all records from 1 - 30 (inclusive) from a table called "Orders". The SQL query would then look like this: $sql = "SELECT * FROM Orders LIMIT 30"; When the SQL query above is run, it will return the first 30 records.


1 Answers

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

like image 95
Haim Evgi Avatar answered Sep 29 '22 17:09

Haim Evgi