MySQL Forums
Forum List  »  Performance

Re: Performance issues afre upgrading to 5.7
Posted by: Øystein Grøvlen
Date: November 25, 2016 06:16AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Performance issues afre upgrading to 5.7
1194
November 25, 2016 06:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.