# Index Used for Select Changed as Data Size Grew

**URL:** <https://forums.percona.com/t/index-used-for-select-changed-as-data-size-grew/102>\
**Category:** Other MySQL® Questions\
**Created:** [October 16, 2006, 5:11pm UTC](https://forums.percona.com/t/index-used-for-select-changed-as-data-size-grew/102 "2006-10-16T17:11:58Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![mbmysql](https://avatars.discourse-cdn.com/v4/letter/m/6f9a4e/32.png) [@mbmysql](https://forums.percona.com/u/mbmysql)\
**Post date:** [October 16, 2006, 5:11pm UTC](https://forums.percona.com/t/index-used-for-select-changed-as-data-size-grew/102/1 "2006-10-16T17:11:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [October 17, 2006, 2:49am UTC](https://forums.percona.com/t/index-used-for-select-changed-as-data-size-grew/102/2 "2006-10-17T02:49:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mbmysql](https://avatars.discourse-cdn.com/v4/letter/m/6f9a4e/32.png) [@mbmysql](https://forums.percona.com/u/mbmysql)\
**Post date:** [October 17, 2006, 1:21pm UTC](https://forums.percona.com/t/index-used-for-select-changed-as-data-size-grew/102/3 "2006-10-17T13:21:42Z")

</div>

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:

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [October 18, 2006, 10:10am UTC](https://forums.percona.com/t/index-used-for-select-changed-as-data-size-grew/102/4 "2006-10-18T10:10:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mbmysql](https://avatars.discourse-cdn.com/v4/letter/m/6f9a4e/32.png) [@mbmysql](https://forums.percona.com/u/mbmysql)\
**Post date:** [October 18, 2006, 12:28pm UTC](https://forums.percona.com/t/index-used-for-select-changed-as-data-size-grew/102/5 "2006-10-18T12:28:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [October 18, 2006, 3:50pm UTC](https://forums.percona.com/t/index-used-for-select-changed-as-data-size-grew/102/6 "2006-10-18T15:50:46Z")

</div>

I hope you understand this solution is very fragile.

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