Menu
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?
525
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.
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.
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.
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.
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
95
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
