Re: Identical servers different plan for the same query

[Eric Coll]

Show table and indexes output (note the master and slave are rarely synced as there's a lot of writing on the master)
+------------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| pool_usage | InnoDB | 10 | Compact | 954061364 | 162 | 155169734656 | 0 | 199728103424 | 32505856 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | partitioned | |
+------------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (4.31 sec)

---------+-----------------+--------------+-----------+----------------+-------- -----+-------------+------------+-----------------+----------+----------------+- --------+
+------------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| pool_usage | InnoDB | 10 | Compact | 938592699 | 165 | 155186528256 | 0 | 174778548224 | 32505856 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | partitioned | |
+------------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (1.91 sec)

+------------+------------+---------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+---------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| pool_usage | 0 | idx_usage_unique | 1 | timestamp | A | 94 | NULL | NULL | | BTREE | | |
| pool_usage | 0 | idx_usage_unique | 2 | zone | A | 94 | NULL | NULL | | BTREE | | |
| pool_usage | 0 | idx_usage_unique | 3 | pool_type_id | A | 2049919 | NULL | NULL | | BTREE | | |
| pool_usage | 0 | idx_usage_unique | 4 | week | A | 2049919 | NULL | NULL | | BTREE | | |
| pool_usage | 0 | idx_usage_unique | 5 | account | A | 953212456 | NULL | NULL | | BTREE | | |
| pool_usage | 0 | idx_usage_unique | 6 | instance_class | A | 953212456 | NULL | NULL | | BTREE | | |
| pool_usage | 0 | idx_usage_unique | 7 | operation | A | 953212456 | NULL | NULL | | BTREE | | |
| pool_usage | 1 | idx_usage_account | 1 | account | A | 94 | NULL | NULL | | BTREE | | |
| pool_usage | 1 | idx_usage_time_region_acccount1 | 1 | timestamp | A | 94 | NULL | NULL | | BTREE | | |
| pool_usage | 1 | idx_usage_time_region_acccount1 | 2 | region | A | 94 | NULL | NULL | | BTREE | | |
| pool_usage | 1 | idx_usage_time_region_acccount1 | 3 | account | A | 953212456 | NULL | NULL | | BTREE | | |
+------------+------------+---------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
11 rows in set (2.14 sec)

+------------+------------+---------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+---------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| pool_usage | 0 | idx_usage_unique | 1 | timestamp | A | 91 | NULL | NULL | | BTREE | | |
| pool_usage | 0 | idx_usage_unique | 2 | zone | A | 91 | NULL | NULL | | BTREE | | |
| pool_usage | 0 | idx_usage_unique | 3 | pool_type_id | A | 22874364 | NULL | NULL | | BTREE | | |
| pool_usage | 0 | idx_usage_unique | 4 | week | A | 22874364 | NULL | NULL | | BTREE | | |
| pool_usage | 0 | idx_usage_unique | 5 | account | A | 983597673 | NULL | NULL | | BTREE | | |
| pool_usage | 0 | idx_usage_unique | 6 | instance_class | A | 983597673 | NULL | NULL | | BTREE | | |
| pool_usage | 0 | idx_usage_unique | 7 | operation | A | 983597673 | NULL | NULL | | BTREE | | |
| pool_usage | 1 | idx_usage_account | 1 | account | A | 91 | NULL | NULL | | BTREE | | |
| pool_usage | 1 | idx_usage_time_region_acccount1 | 1 | timestamp | A | 91 | NULL | NULL | | BTREE | | |
| pool_usage | 1 | idx_usage_time_region_acccount1 | 2 | region | A | 91 | NULL | NULL | | BTREE | | |
| pool_usage | 1 | idx_usage_time_region_acccount1 | 3 | account | A | 983597673 | NULL | NULL | | BTREE | | |
+------------+------------+---------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
11 rows in set (4.23 sec)