Hi,
The bug report for this new behavior is
http://bugs.mysql.com/bug.php?id=80580. What happens in 5.7 is that InnoDB will always use a full table scan to count the number of records. Earlier, the optimizer would select the shortest (in # of pages) index and do the counting by reading from that index. In 5.7, it asks the storage engine during the optimization phase for the number of records in the table, and it is up the storage engine to provide the count. (That the call is made in the optimization phase is the reason that you see that it will be in "optimizing state" for a long time.)
The advantage of the new approach is that server does not have to read every index entry into the server to do the counting. Also, due to how multi-versioning (MMVCC) is implemented in InnoDB, index scans may take longer than table scans even if the index is smaller. The disadvantage is, in the InnoDB case, that it takes longer to count the rows when the table is much larger than some index.
We have just fixed
http://bugs.mysql.com/bug.php?id=81854 so that in 5.7.18 it will be possible to override the new behavior by using FORCE INDEX. I will suggest that as a work-around.
Thanks,
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway
Edited 1 time(s). Last edit at 11/25/2016 11:02AM by Edwin Desouza.