Index Used for Select Changed as Data Size Grew

MySQL & MariaDB Other MySQL® Questions
1

Database info:

  • MySQL 5.0
  • InnoDB tablespace
  • Over 500 GB of data

Background:
A common query in the application was running smoothly until recently the query began using the wrong index. Before it was using the right index but then as our dataset grew larger it changed the index it was using for some reason.

If I tell the query to USE INDEX (correct_index) then the query works fine. However, I’d like to have it set up so I don’t have to use the USE INDEX syntax in the query.

Question:
What can I do to investigate why the query is using the wrong index? I’ve tried running ANALYZE TABLE [all tables involved] but nothing changed when running EXPLAIN on the query.

Is it worth it to try removing the index and then reapplying the index? This may not be a reasonable solution due to the length of time it would take to rebuild the index on my large dataset.

Is there some other way to get MySQL to use the correct index? Maybe by altering the index and then undoing the changes so MySQL re-reads the index.

Any help is appreciated…thanks in advance!

– Brad

2

With Innodb tables ANALYZE table is what updates the stats. It however does it by random dives rather than by scan, like MyISAM.

However besides ANALYZE stats MySQL also uses btree dives estimating number of rows in range, if it is possible.

It would be helpful if you could send EXPLAIN for the query you have in mind with and without USE INDEX.

It is important to understand reason for the problem - did your data distribution really change ? Is index layout changed so estimate MySQL does is badly wrong ? Is it optimizer by or anything.

Before we really know what caused it to change the plan it is hard to advice on how to cure it

3

Thanks for the reply and I’ve added more detailed information:

With the first query I don’t specify the index and MySQL selects the wrong index, index2. Then when I specify the index, index3, MySQL uses the correct index.

I’d like to not have to specify which index to use in my code and would rather have MySQL select the correct index for me. Is there any steps I can take to help MySQL correct itself using ANALYZE or INDEX or any other statements or tools?

Please note: at the very end is the details of each of the 2 indexes in question.

Thanks in advance for any help!

– Brad

Query without specifying the index:

SELECT
db1.table1.field1 , db1.table1.field2 , db1.table2.field3 , db1.table1.field4 , db1.table1.field5 , db1.table1.field6 , db2.table4.field7 , db1.table1.field8 , db1.table3.field9 , db1.table1.field10 , db1.table3.field11 , db1.table3.field12 , db1.table3.field13
FROM db1.table1
INNER JOIN db1.table3 ON db1.table3.field14 = db1.table1.field14
INNER JOIN db2.table4 ON db2.table4.field7=db1.table3.field15
INNER JOIN db1.table2 ON db1.table2.field16 = db1.table1.field17
INNER JOIN db2.table5 ON db2.table5.field7 = db2.table4.field7
WHERE ( ( ( db1.table1.field18=0 ) ) AND ( ( db1.table1.field19=0 ) ) AND ( ( ( ( db2.table5.field20 LIKE ‘%SomeText%’ ) ) ) ) ) ORDER BY field4 DESC, field5 DESC LIMIT 40,20;

Explain of above Query which doesn’t specify the index:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: table2
type: index
possible_keys: PRIMARY
key: index1
key_len: 753
ref: NULL
rows: 51180
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: table
type: ref
possible_keys: index2, index3, index4
key: index2
key_len: 11
ref: db1.table2.field16,const
rows: 7
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: table3
type: eq_ref
possible_keys: PRIMARY,index5
key: PRIMARY
key_len: 8
ref: db1.table1.field14
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: table5
type: ref
possible_keys: index6
key: index6
key_len: 9
ref: db1.tabl3.field15
rows: 2
Extra: Using where
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: table4
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: db2.table5.field7
rows: 1
Extra: Using where; Using index

Query when specifying which index to use:

SELECT
db1.table1.field1 , db1.table1.field2 , db1.table2.field3 , db1.table1.field4 , db1.table1.field5 , db1.table1.field6 , db2.table4.field7 , db1.table1.field8 , db1.table3.field9 , db1.table1.field10 , db1.table3.field11 , db1.table3.field12 , db1.table3.field13
FROM db1.table1 USE INDEX (index3)
INNER JOIN db1.table3 ON db1.table3.field14 = db1.table1.field14
INNER JOIN db2.table4 ON db2.table4.field7=db1.table3.field15
INNER JOIN db1.table2 ON db1.table2.field16 = db1.table1.field17
INNER JOIN db2.table5 ON db2.table5.field7 = db2.table4.field7
WHERE ( ( ( db1.table1.field18=0 ) ) AND ( ( db1.table1.field19=0 ) ) AND ( ( ( ( db2.table5.field20 LIKE ‘%SomeText%’ ) ) ) ) ) ORDER BY field4 DESC, field5 DESC LIMIT 40,20;

Explain of Query when specifying index:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: table1
type: ref
possible_keys: index3
key: index3
key_len: 4
ref: const,const
rows: 504846
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: table3
type: eq_ref
possible_keys: PRIMARY,index5
key: PRIMARY
key_len: 8
ref: db1.table1.field14
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: table2
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: db1.table1.field17
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: table4
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: db1.table3.field15
rows: 1
Extra: Using index
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: table5
type: ref
possible_keys: index7
key: index7
key_len: 9
ref: db2.table4.field7
rows: 1
Extra: Using where

Corresponding details for index2 and index3 which correspond to table1:

*************************** 3. row ***************************
Table: table1
Non_unique: 1
Key_name: index2
Seq_in_index: 1
Column_name: field17
Collation: A
Cardinality: 116856
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 4. row ***************************
Table: table1
Non_unique: 1
Key_name: index2
Seq_in_index: 2
Column_name: field18
Collation: A
Cardinality: 150244
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 5. row ***************************
Table: table1
Non_unique: 1
Key_name: index2
Seq_in_index: 3
Column_name: field4
Collation: A
Cardinality: 1051712
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 6. row ***************************
Table: table1
Non_unique: 1
Key_name: index2
Seq_in_index: 4
Column_name: field5
Collation: A
Cardinality: 1051712
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 7. row ***************************
Table: table1
Non_unique: 1
Key_name: index3
Seq_in_index: 1
Column_name: field19
Collation: A
Cardinality: 22
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 8. row ***************************
Table: table1
Non_unique: 1
Key_name: index3
Seq_in_index: 2
Column_name: field18
Collation: A
Cardinality: 22
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 9. row ***************************
Table: table1
Non_unique: 1
Key_name: index3
Seq_in_index: 3
Column_name: field4
Collation: A
Cardinality: 1051712
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 10. row ***************************
Table: table1
Non_unique: 1
Key_name: index3
Seq_in_index: 4
Column_name: field5
Collation: A
Cardinality: 1051712
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

4

In this case it is far more than simply different index.
Join order is different as well as I can see.

The plan MySQL proposes is not bad if you compare it to the previous plan directly. But as it uses filesort which makes LIMIT unhelpful it becomes much slower.

MySQL does not take ORDER BY … LIMIT into account when selecting table join order which is what causes problem in this case.

5

Interesting and thanks for the feedback.

I was able to get the query to use the correct index after running analyze table on the table numerous (10-15) times. In the case of InnoDB running analyze table returns a different result each time. So after each run I was able to check if it made a difference in the explain of the query and after a bunch of attempts it finally made a difference.

6

I hope you understand this solution is very fragile.

Innodb automatically runs Analyze when table is opened first time after server restart.