Mysql – index not being used

indexMySQL

I have an exact same myisam table on master and slave. The indexes are not being used on Master server.

mysql> explain select count(date_time) from ox_data_summary_ad_hourly where  date_time = '2012-03-08 00:00:00' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ox_data_summary_ad_hourly
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 32212292
        Extra: Using where
1 row in set (0.00 sec)

The explain plan on the slave:

mysql> explain select count(date_time) from ox_data_summary_ad_hourly where  date_time = '2012-03-08 00:00:00' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ox_data_summary_ad_hourly
         type: ref
possible_keys: ox_data_summary_ad_hourly_date_time
          key: ox_data_summary_ad_hourly_date_time
      key_len: 8
          ref: const
         rows: 156769
        Extra: Using index
1 row in set (0.00 sec)

The master is 5.0 while slave is on 5.5 version if that matters.

Does it mean the index file on master is corrupt?

Best Answer

The usual rule of thumb for MySQL Query Optimizer is this:

If the EXPLAIN plan has to read more than 5% of the table rows via the index, the index is dismissed (ruled out) and a full table scan is performed.

In the case of the Slave, 156769 out of 32212292 rows are to be read. That's 0.4867%. Hence, the index is used.

With regard to the Master, it is very disturbing that the MySQL Query Optimizer ruled out indexes. As @ypercube commented, perhaps there is no index.

You should make sure by running SHOW CREATE TABLE ox_data_summary_ad_hourly\G on both Master and Slave. If they are different, please post both outputs in the question.

Also, please run SELECT COUNT(1) FROM ox_data_summary_ad_hourly; on both Master and Slave and post these counts

Related Question