Avoid full table scan in mysql while using order by with limit

Question

I m writing a query:

select * from table_name order by id limit 21

Until i use the limit is lesser or equal to 20 the rows getting scanned is equal to the exact fetching rows (for example if the limit is 10, than the scanned rows also 10 only). If the limit exceeds 20 the table getting scanned fully.

The only one index created for the primary key id only. Can anybody tell the reason for the full table scan in this case?

---

My table has 1099 rows.

Explain Result:
---------------------------------------------------------------------------
id|selecttype|table |type|possiblekeys|keys|key_len|ref |rows|  Extra       
---------------------------------------------------------------------------
1 | SIMPLE   |tablen|ALL |  null      |null|null   |null|1099|Usingfilesort 
---------------------------------------------------------------------------

Answer 1

In general case, to return rows for a LIMIT M, N MySQL will have to scan M+N rows of the result without LIMIT, and skip the first M-1 of them, hence full table scan.

The first 20 rows in your case seem to fit into a single page, and since you order by the primary key, MySQL probably understands that it won't need to make full table scan.

Another thing one should know, MySQL usually does not use any indexes for queries like SELECT * FROM T ORDER BY something, indexes are usually used when there is a condition, or if all the data can be fetched from the index directly (covering indexes).