Announcement

Announcement Module
Collapse
No announcement yet.

Index Used for Select Changed as Data Size Grew

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Index Used for Select Changed as Data Size Grew

    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

    Comment


    • #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:

      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.

        Comment


        • #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.

          Comment


          • #6
            I hope you understand this solution is very fragile.

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

            Comment

            Working...
            X