6

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 
---------------------------------------------------------------------------
Tim Post
  • 33,371
  • 15
  • 110
  • 174
Sangeetha Krishnan
  • 941
  • 1
  • 12
  • 17
  • Are you sure you have an index on `table_name(id)`? –  Jan 18 '12 at 11:20
  • Can you test your query like this: select id from table_name order by id limit 21 and please add to your question explain results and table structure – mirza Jan 18 '12 at 11:25
  • Is the query slow? How many rows does the table have? If you have only a few hundred rows, then doing a whole scan for `LIMIT 100` is what the optimizer thinks as faster. – ypercubeᵀᴹ Jan 18 '12 at 11:40
  • please provide structure of your table – Fahim Parkar Jan 18 '12 at 12:06
  • how do you know that only N number of rows are scanned? I am not much in mysql so asking... – Fahim Parkar Jan 18 '12 at 14:19
  • 2
    Please do not post replies as answers, edit your question if you want to add more information, or leave comments under the answers you receive. – Tim Post Jan 21 '12 at 13:58

2 Answers2

6

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).

newtover
  • 31,286
  • 11
  • 84
  • 89
  • thanks for ur information. is there any other way to restrict scan in this case. – Sangeetha Krishnan Jan 21 '12 at 09:22
  • Hi @newtovar, Can you please look my question this is related to your answer and i'm using index column in codition but getting same scan problem. Here is the link https://stackoverflow.com/questions/53350515/how-to-use-limit-in-query-on-my-index-column-without-scan-all-rows – Shubham Nov 19 '18 at 13:24
2

When your primary key id gets incremented by one, you should be able to do the following query using a WHERE clause to get the data based on id.

SELECT * FROM table_name WHERE id BETWEEN 0 AND 21 ORDER BY id

or

SELECT * FROM table_name WHERE id >= 0 LIMIT 21;

But your IDs have to be incremented strictly by one. When a row was removed and there are gaps in the id column, this might lead to unwanted results. WHERE id BETWEEN x AND y might show less or no results. WHERE id => x AND LIMIT y might show repeated results on the “next page” when x is strictly incremented by y.

Simon
  • 21
  • 4