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.

As a complement to what @Rick James said about getting more info is that if you have Performance Schema installed and enabled, you can execute the query in MySQL Workbench with enough privileges and then get detailed execution info through the GUI (see the section “Query Stats” after executing your query). You only need the monitoring level “Server Default” for Performance Schema to do this automatically. Suitable in a situation like yours and for other fast queries, especially in a staging environment or on a dev machine.

When I execute the query of yours in a replicated setup I see in the report that only 3 rows are examined.