Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I'm sure I've seen a bug report for this for 5.5. Trying to find it... –  gbn Mar 9 '12 at 8:32
    
@gbn: It is not using the index on the 5.0 machine. And the possible_keys: NULL means that there is no index in the table in that machine, doesn't it? –  ypercube Mar 9 '12 at 11:02
    
@ypercube: good point, should be answer... –  gbn Mar 9 '12 at 11:47
    
Please run SHOW CREATE TABLE ox_data_summary_ad_hourly\G on Master and Slave. If they are different, please post them both in your question. –  RolandoMySQLDBA Mar 9 '12 at 15:42
    
Both are "exactly" the same tables. –  shantanuo Mar 11 '12 at 3:14

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.