-1

I have the following setup:

create table Test(id integer, itemID integer, title varchar(100) ,PRIMARY KEY (id, itemID));
insert into Test(id, itemID, title) values(1,678, "Hello");
insert into Test(id, itemID, title) values(1,679, "how");
insert into Test(id, itemID, title) values(1,680, "are");
insert into Test(id, itemID, title) values(2,692, "you");
insert into Test(id, itemID, title) values(2,693, "iam");

And I'm running this query:

Explain select * from Test where id IN (1,2,3) AND itemID IN (679,692);

In v8.0.12 I'm getting 6 in the Rows column. Is it a bug? Since there are only 5 rows.

In v8.0.21 I'm getting 5.

  • 2
    Please consider including everything that's needed to answer your question in the question itself. Once the link you have included goes stale, your question as is won't make sense. – mustaccio Sep 14 at 19:06
1

That actually depends on which version of mysql you use

https://www.db-fiddle.com/f/4yPorU6k3SjQ5nmhgi1wGo/23

mysql 8 estimates 6 rows
and mysql 5.7 and 5.6 think it must examine 5 rows

The definition of rows is

Estimate of rows to be examined

see https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-output-columns

This means:

That it only gives the estimated number of rows, that MySQL believes it must examine from the examined table, to execute the query.

This is as the description already tells you only an estimated number.

if you have a high row count then there is some room for optimization. As lower rows count means faster queries

| improve this answer | |
  • Can one know the source code for how it estimates? – Smart Manoj Sep 15 at 2:26
  • In v8.0.21 returns 5 too – Smart Manoj Sep 15 at 2:47
  • that depends not only on source code version version but also on operating system and compiler. – nbk Sep 15 at 7:45
  • Can one see what are the rows to be examined? – Smart Manoj Sep 15 at 13:40
  • Even approximation , how it returns 6 since there is only 5 rows. – Smart Manoj Sep 15 at 13:41

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.