Query picks different indexes on different servers

MySQL & MariaDB Other MySQL® Questions
1

My master database is where all of my inserts/updates go, along with a small subset of my selects. Two slave servers handle the bulk of the selects.

I have a query that joins 4 tables. On my master database, the query planner picks a poor index for the query, and the query takes about 15 minutes to run. On the slaves, a better index is picked, and the query runs in about 2 seconds.

Running analyze table on the master did not change the behavior, even with innodb_stats_sample_pages turned up to a high value (512). However, I did find a fix, which was to go onto the master, and do some selects which read in the whole table. These sorts of full table scans never happen on the master under ordinary circumstances. After that, the query optimizer started to pick the right indexes!

What I’m wondering is why that would be? What all goes into the heuristic used by the query planner to pick the best index for a select? Just the cardinality of each index, or is there more to it, such as how often each index is used? Is there anything other than ANALYZE TABLE that I can do to keep the statistics in good shape in my setup?

I’m running Percona Server 5.5.27-rel28.1-296 for Ubuntu on EC2. I’ve noticed that when I build a test server using an EBS snapshot of my master database, this problem continues to manifest itself. Are table statistics persisted to disk in Percona Server 5.5?

–Michael Mittelstadt
Director of Systems Engineering, Get Satisfaction.

2

Hi,

As you know that MySQL optimizer makes the decision of what execution plan to use, based on the information provided by the storage engines. That information is not accurate in some engines like InnoDB and they are based in statistics calculations. So in InnoDB it’s just estimation of cardinality and it can not be the same on different server sometimes. Can you provide some more information about this issue?

  1. Are MySQL versions same on both master and slave servers?
  2. Can you check if rows, table structures and indexes are same (for those 4 tables) on master and slave servers?
  3. Can you provide us those 4 table structures , explain plans and show indexes on master and slave servers?
  4. Are you using many null values in those tables? Because it’s possible with many null values that many pages statistics and default innodb_stats_method=nulls_equal could cause issues on many pages scanned.
    [URL]http://dev.mysql.com/doc/refman/5.5/en/innodb-other-changes-statistics-estimation.html[/URL]

Thanks.