MySQL Forums :: Performance :: Performance issues afre upgrading to 5.7


Advanced Search

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
Performance issues afre upgrading to 5.7 364 Alexandru Badulescu 11/24/2016 08:48AM
Re: Performance issues afre upgrading to 5.7 277 Peter Brawley 11/24/2016 11:32AM
Re: Performance issues afre upgrading to 5.7 184 Øystein Grøvlen 11/25/2016 07:28AM
Re: Performance issues afre upgrading to 5.7 197 Øystein Grøvlen 11/25/2016 06:16AM
Re: Performance issues afre upgrading to 5.7 189 Alexandru Badulescu 11/25/2016 06:59AM
Re: Performance issues afre upgrading to 5.7 274 Øystein Grøvlen 11/25/2016 07:53AM
Re: Performance issues afre upgrading to 5.7 187 Rick James 11/25/2016 08:18PM


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.