Here is my table :
In my table
Clustering_key(Primary key and auto incremental)
Data(Text datatype column)
Position(Index column) maintain the order of
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
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 ...;
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.