When MySQL EXPLAIN Estimates Go Wrong

An odd looking bug with EXPLAIN in MySQL 5.1 mentioned in When EXPLAIN estimates can go wrong!

the row estimates on 5.1 is very off, its as much as 57 times less than the number of rows, which is not acceptable at all. While 5.5 returns an acceptable estimate.

This test proves that there is a bug in MySQL 5.1 and how it calculates the row estimates. This bug was tracked down to http://bugs.mysql.com/bug.php?id=53761 and was indeed fixed in 5.5 as my tests show.

Which leads MySQL 5.1 to make some odd index choices based on wildly inaccurate row estimates. Good to know this has already been fixed in MySQL 5.5.

2 Comments

  1. I always wondered. Sometimes I thought they could be wildly off under certain circumstances. Glad I’m on Percona Server 5.5.

  2. Definitely strange. If I were to build a new MySQL server I’d probably go with latest Percona release as well.

Leave a Reply

Your email address will not be published. Required fields are marked *

© 2018 Joseph Scott

Theme by Anders NorénUp ↑