According to MySQL document:
MySQL uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant. In addition, key distributions can be used when deciding which indexes to use for a specific table within a query.
So one quick solution to ask the query to use the index again, it is to run "Analyze Table" command.
According to MySQL document:
ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for InnoDB and MyISAM.
According to my experience, it doesn't take long time to run "Analyze Table", and it dose solve the problem. for example:
Analyze local table user_contact;
Analyze local table contacts;
Analyze local table historyv2;
Analyze local table user;
Analyze local table sent;
Analyze local table email;
Analyze local table custom_value;
Analyze local table custom_attribute;
You can create a weekly event to do it automatically:
This can save your life for some time. But eventually it will not work. When the time comes, it is for you to do:
For MySQL 5.6, hopefully we could reduce this problem by using
Persistent Optimizer Statistics for InnoDB Tables
Plan stability is a desirable goal for your biggest and most important queries. InnoDB has always computed statistics for each InnoDB table to help the optimizer find the most efficient query execution plan. Now you can make these statistics persistent, so that the index usage and join order for a particular query is less likely to change.
This feature is on by default, enabled by the configuration option innodb_stats_persistent.
You control how much sampling is done to collect the statistics by setting the configuration options innodb_stats_persistent_sample_pages and innodb_stats_transient_sample_pages.
The configuration option innodb_stats_auto_recalc determines whether the statistics are calculated automatically whenever a table undergoes substantial changes (to more than 10% of the rows). If that setting is disabled, ensure the accuracy of optimizer statistics by issuing the ANALYZE TABLE statement for each applicable table after creating an index or making substantial changes to indexed columns. You might run this statement in your setup scripts after representative data has been loaded into the table, and run it periodically after DML operations significantly change the contents of indexed columns, or on a schedule at times of low activity.
To ensure statistics are gathered when a new index is created, either enable the innodb_stats_auto_recalc option, or run ANALYZE TABLE after creating each new index when the persistent statistics mode is enabled.
You can also set innodb_stats_persistent, innodb_stats_auto_recalc, and innodb_stats_transient_sample_pages options at the session level before creating a table, or use the STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES clauses on the CREATE TABLE and ALTER TABLE statements, to override the system-wide setting and configure persistent statistics for individual tables.
Formerly, these statistics were cleared on each server restart and after some other operations, and recomputed when the table was next accessed. The statistics are computed using a random sampling technique that could produce different estimates the next time, leading to different choices in the execution plan and thus variations in query performance.
To revert to the previous method of collecting statistics that are periodically erased, run the command ALTER TABLE tbl_name STATS_PERSISTENT=0.
The persistent statistics feature relies on the internally managed tables in the mysql database, named innodb_table_stats and innodb_index_stats. These tables are set up automatically in all install, upgrade, and build-from-source procedures.
The innodb_table_stats and innodb_index_stats tables both include a last_update column showing when index statistics were last updated, as shown in the following example:
If you manually update the statistics in the tables during troubleshooting or tuning, issue the command FLUSH TABLE tbl_name to make MySQL reload the updated statistics.
mysql> selectfrom INNODB_TABLE_STATS \G 1. row database_name: sakila table_name: actor last_update: 2014-05-28 16:16:44 n_rows: 200 clustered_index_size: 1 sum_of_other_index_sizes: 1 ... mysql> select from INNODB_INDEX_STATS \G 1. row database_name: sakila table_name: actor index_name: PRIMARY last_update: 2014-05-28 16:16:44 stat_name: n_diff_pfx01 stat_value: 200 sample_size: 1 ...