index not being used

Question

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?

Answer 1

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