Description:
A regression happened sometime after 5.0.24a. In 5.0.24 the correct index is picked for the following query:
EXPLAIN select count(*) from MY_TABLE
-> WHERE MY_DATE >= current_date() - interval 1 day
-> AND MY_DATE < current_date()
-> AND survey_number = '00119'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MY_TABLE
type: range
possible_keys: idx_my_table_DATE,idx_my_table_survey,idx_my_table_survey_tn_serv_assoc
key: idx_my_table_DATE
key_len: 9
ref: NULL
rows: 1
Extra: Using where
1 row in set
select count(*) from MY_TABLE
-> WHERE MY_DATE >= current_date() - interval 1 day
-> AND MY_DATE < current_date()
-> AND survey_number = '00119';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.03 sec)
5.0 now chooses the wrong index which has terrible consequences:
mysql> EXPLAIN select count(*) from MY_TABLE
-> WHERE MY_DATE >= current_date() - interval 1 day
-> AND MY_DATE < current_date()
-> AND survey_number = '00119'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MY_TABLE
type: ref
possible_keys: idx_my_table_DATE,idx_my_table_survey,idx_my_table_survey_tn_serv_assoc
key: idx_my_table_survey_tn_serv_assoc
key_len: 7
ref: const
rows: 142264
Extra: Using where
1 row in set
It's choosing idx_my_table_survey_tn_serv_assoc which is an index
on (survey_number, tn_serviced, associate_id) when only survey_number is
being referenced. From the key_len it's only using the first portion of
the index.
It seems like it's taking the high cardinality of that index (4670577)
and using it even though the cardinality is coming from the three columns
combined (idx_my_table_survey only has a cardinality of 38!).
Here's the show indexes output for the table:
*************************** 1. row ***************************
Table: MY_TABLE
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: DOMAINID
Collation: A
Cardinality: 9341155
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: MY_TABLE
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: VRU_ID
Collation: A
Cardinality: 9341155
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: MY_TABLE
Non_unique: 1
Key_name: idx_my_table_SENT
Seq_in_index: 1
Column_name: SENT
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 4. row ***************************
Table: MY_TABLE
Non_unique: 1
Key_name: idx_my_table_DATE
Seq_in_index: 1
Column_name: MY_DATE
Collation: A
Cardinality: 2335288
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 5. row ***************************
Table: MY_TABLE
Non_unique: 1
Key_name: idx_my_table_CLIENTNUM
Seq_in_index: 1
Column_name: CLIENTNUM
Collation: A
Cardinality: 16
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 6. row ***************************
Table: MY_TABLE
Non_unique: 1
Key_name: idx_my_table_TRANSCRIBED
Seq_in_index: 1
Column_name: VC_RECORDED
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 7. row ***************************
Table: MY_TABLE
Non_unique: 1
Key_name: idx_my_table_TRANSCRIBED
Seq_in_index: 2
Column_name: TRANSCRIBED
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 8. row ***************************
Table: MY_TABLE
Non_unique: 1
Key_name: idx_my_table_tn_called
Seq_in_index: 1
Column_name: TN_CALLED
Collation: A
Cardinality: 4670577
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 9. row ***************************
Table: MY_TABLE
Non_unique: 1
Key_name: idx_my_table_survey
Seq_in_index: 1
Column_name: SURVEY_NUMBER
Collation: A
Cardinality: 28
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 10. row ***************************
Table: MY_TABLE
Non_unique: 1
Key_name: idx_my_table_FILE_NAME
Seq_in_index: 1
Column_name: FILE_NAME
Collation: A
Cardinality: 9341155
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 11. row ***************************
Table: MY_TABLE
Non_unique: 1
Key_name: idx_my_table_FILE_NAME2
Seq_in_index: 1
Column_name: FILE_NAME2
Collation: A
Cardinality: 9341155
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 12. row ***************************
Table: MY_TABLE
Non_unique: 1
Key_name: idx_my_table_survey_tn_serv_assoc
Seq_in_index: 1
Column_name: SURVEY_NUMBER
Collation: A
Cardinality: 28
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 13. row ***************************
Table: MY_TABLE
Non_unique: 1
Key_name: idx_my_table_survey_tn_serv_assoc
Seq_in_index: 2
Column_name: TN_SERVICED
Collation: A
Cardinality: 4670577
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 14. row ***************************
Table: MY_TABLE
Non_unique: 1
Key_name: idx_my_table_survey_tn_serv_assoc
Seq_in_index: 3
Column_name: ASSOCIATE_ID
Collation: A
Cardinality: 4670577
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
How to repeat:
See Description. If you need a test table that displays the same problem I can
work on that. Let me know.
