A little clarification on how 'LIMIT' works

Not being a full blooded member of the DBA clan, but more of a dev clan, I find myself trying to figure out some slow running queries.

The subject of my question however is not 'Why my queries are slow' , but one of wanting to know how the 'LIMIT' command in MySql works.

At the moment, I'm still trying to troubleshoot the server performance itself, so as a way of giving it a little SQL to chew on (Just to make it do some work) I'm using the following:

SELECT * FROM  LIMIT 10

The table I'm using is a live table with an excess of 50Mil rows in it, and as I'm only conducting tests at the moment my expectation is this will return the first 10 records off the top of the table, and would easily do so in under 10 minutes.

However...

I'm seeing this SQL still running after 30 mins or more, and appearing not to do anything.

What I want to clarify specifically is this:

Does MySql attempt to fetch the entire table from a DB before then just returning the first 10 rows

OR

Does MySql fetch rows as it receives them and stop after it counts that it's recieved 10 rows

Cheers
Shawty

mysql should optimize a query like your example and not do a full table scan just to retrieve 10 records. Adding an order by without an index before the limit 10 would perform a full table scan.

Are you sure that your "table" is really a table and not a view? You might be selecting from a slow view.

StackExchange Database Administrators Q#62439, answer score: 6