Not the answer you need?
Register and ask your own question!

Index Used for Select Changed as Data Size Grew

mbmysqlmbmysql EntrantInactive User Role Beginner
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

Comments

  • PeterPeter Percona CEO Percona Moderator Role
    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
  • mbmysqlmbmysql Entrant Inactive User Role Beginner
    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:
  • PeterPeter Percona CEO Percona Moderator Role
    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.
  • mbmysqlmbmysql Entrant Inactive User Role Beginner
    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.
  • PeterPeter Percona CEO Percona Moderator Role
    I hope you understand this solution is very fragile.

    Innodb automatically runs Analyze when table is opened first time after server restart.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.