How to use Limit in query on my index column without scan all rows?

Here is my table :

In my table

• Clustering_key (Primary key and auto incremental)

• ID (Index Column)

• Data (Text datatype column)

• Position(Index column) maintain the order of Data

My table have 90,000 rows with same ID equal to 5. I want to fetch first 3 rows and my query like this

Select * from mytable where ID=5 Limit 3;

ID column is index column So I think mysql scan only first 3 rows but mysql scan around 42000 rows.

Here Explain query :

Any possibility to avoid all rows scan.

Please give me some solution

Thanks in advance

EXPLAIN is not as smart as one would like. It provided 42415 without noticing your LIMIT 3.

The important clue that says that it will use the index is the Key column lists the index name.

That query, with that index (I assume ID is a numeric datatype) will touch only 3 rows of the index, then 3 rows of the data.

Two ways to get more info:

EXPLAIN FORMAT=JSON SELECT ...;

or

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';

I would expect to see 2 (3-1) in one or two rows, and not see a number that matches the table size.

StackExchange Database Administrators Q#222951, answer score: 3