Hi,
I have an innodb table with about 8 million rows of data that has
several indexes defined. While performing a select MySQL almost always
selects the most efficient index but it will occasionally select an
index that is not efficient. How can I determine what is going on
behind the scenes in MySQL that makes it determine which index to use.
I have been using explain with my select statement and I can see that
the index used is changing but I do not know why. Here is an example.

Efficient index:
mysql> explain SELECT count(*) from mytable WHERE recdate BETWEEN
20060701000000 AND 20060710235959 AND cust=999999\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mytable
type: range
possible_keys: recdate_i,cust_i
key: recdate_i
key_len: 3
ref: NULL
rows: 67884
Extra: Using where
1 row in set (0.00 sec)

Not efficient index:
mysql> explain SELECT count(*) from mytable WHERE recdate BETWEEN
20060601000000 AND 20060610235959 AND cust=999999\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: qcal
type: ref
possible_keys: recdate_i,cust_i
key: cust_i
key_len: 3
ref: const
rows: 719680
Extra: Using where
1 row in set (0.00 sec)

The only difference in the queries is the date range (July in one and
June in another). Both date ranges have very similar total records to
sort through as well but as you can see, the 2nd query decides to look
through 700k + rows while the 1st only looks through 67k + rows.

Thanks