3

Here is my table :

enter image description here

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 :

enter image description here

Any possibility to avoid all rows scan.

Please give me some solution

Thanks in advance

  • Try to add ORDER BY clustering_key ASC clause to the query. – Kondybas Nov 20 '18 at 9:35
  • In the documentation it says that "rows" is an estimate, does the estimate take limit into consideration? – Lennart Nov 20 '18 at 9:48
  • @Kondybas In my situation clustering_key is an auto incremental column and also used as primary key. I don't want to use it in my query. As per your suggestion I tried your solution and added ORDER BY clustering_key ASC in my query but results are same. See this imgur.com/pPfP1au – Shubham Nov 20 '18 at 12:20
  • @Lennart Yes you are right "rows" is an estimate and InnoDB does not keep an internal count of rows in a table. Can you please give me more explanation about relation between concept of row estimation with my problem. – Shubham Nov 20 '18 at 13:03
  • @Shubham, I dont know much about innodb, but if your table contains 90000 rows and about half of them have id=5, the cardinality for id is 2, and the expected number of rows for id=5 is 90000/2=45000. If the optimizer does not take limit into concideration, estimated rows seems reasonable. Thats the estimate part, what happens when you execute the query is another. I don't know if you can inspect how many rows the query actually touches, it might not be more than 3. As mentioned I don't know much about innodb, hopefully someone with greater insight can reveal if limit is concidered for rows. – Lennart Nov 20 '18 at 13:24
2

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.