MySQL Forums
Forum List  »  Optimizer & Parser

full table scan for limit??
Posted by: h ch
Date: December 15, 2009 01:49AM

i am very puzzle about the question if the limit will let SQL optimizer to do full table scan.
and i read the following text from mysql-administation bible by someone,it says

Although the LIMIT clause can be useful, its implementation is very basic. In order to retrieve the information, mysqld processes a query as if there were no LIMIT, and stops when it reaches the row count it needs to.

that means when the query like " select * from mytable limit N" executing,it will look the data it want,but when after it reached the N'th record,it stop looking ,and just quit. that's means it did not do full table scan,

but i also readed another text about SQL_CALC_FOUND_ROWS usage,that's means it did do the full table scan,which one is right??can anybody help?

mysql> SELECT SQL_CALC_FOUND_ROWS rental_date, inventory_id,
-> customer_id, return_date FROM RENTAL LIMIT 1\G
mysql> SELECT FOUND_ROWS();
+--------------+
| found_rows() |
+--------------+
| 16044 |
+--------------+

mysql> SELECT COUNT(*) FROM RENTAL;
+----------+
| count(*) |
+----------+
| 16044 |
+----------+

Options: ReplyQuote


Subject
Views
Written By
Posted
full table scan for limit??
5797
December 15, 2009 01:49AM
2822
December 22, 2009 12:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.