15

I have a table of 200k entries with columns of INT's. I want to create an index to make queries faster. This is the query I would like to execute: SELECT A,B,C,D,E FROM table WHERE A=23 and (B=45 or C=43). I created the following indexes: B, ACD, C, ABC.

With the EXPLAIN command I found that MySQL chooses the index ACD. So I kept populating the table with more values and I realized that MySQL was switching between the indexes above (not always the same one).

Since there are many inserts, having various indexes will cause performance issues and we can assume that this table is accessed by other queries that require different columns where every index makes sense.

I am aware of the USE INDEX(), but I would like to understand if we should trust MySQL to choose the right index.

dda
  • 6,030
  • 2
  • 25
  • 34
Daniel Vila Boa
  • 670
  • 5
  • 13
  • 2
    What do you mean by "we can assume that this table is accessed by other queries that require different columns where every INDEX makes sense"? Could you give some examples of such queries? Can you also give us the output of a `SHOW INDEXES FROM table` query? The indexes on A, AB and AC are certainly redundant: these are already covered by the indexes on ACD/ABC, ABC and ACD respectively. You can read about how MySQL handles multi-column indexes at http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html – Daan Mar 22 '12 at 20:59
  • Example query: `GET B FROM table WHERE B=12`. Ok, I can eliminate A, AB, AC (thanks). But it still doesn't explain the reason for using `ACD`. When `D` isn't even in the `WHERE` statement. – Daniel Vila Boa Mar 22 '12 at 21:11
  • Yes, that's a bit mysterious: the output of a `SHOW INDEXES FROM table` query may help to see why that is happening :) – Daan Mar 22 '12 at 21:24
  • SHOW INDEXES test_index_table [Table] => test_index_table, [Non_unique] => 0, [Key_name] => PRIMARY, [Seq_in_index] => 1, [Column_name] => ID, [Collation] => A, [Cardinality] => 100567, [Sub_part] => , [Packed] => , [Null] => , [Index_type] => BTREE, [Comment] =>. – Daniel Vila Boa Mar 22 '12 at 21:51
  • I think a UNION select is probably the fastest way to solve this, as you've already figured out. You could also experiment with `SELECT A,B,C,D,E FROM table WHERE (A=23 AND B=45) OR (A=23 AND C=43)`. I'm not sure what MySQL will think of that in terms of picking an index. I think, but am not sure, this would probably result in a full table scan...plus there's no real benefits to this approach rather than using UNION, so I'd suggest you simply use UNION instead :) – Daan Mar 22 '12 at 22:04
  • 200k rows is pretty minimal to optimize indexes. Do you expect this to grow? – Jé Queue Mar 22 '12 at 23:54

2 Answers2

9

Because of the OR in the SQL statement, MySQL is simply getting the first index that includes A, which is ACD.

I came to the conclusion that the way to solve this issue using an INDEX is to make two separate queries. SELECT A,B,C,D,E FROM table WHERE A=23 AND B=45 which will use the INDEX ABC and then SELECT A,B,C,D,E FROM table WHERE A=23 AND C=43 which will use INDEX ACD. This can all be done in one step with (...) UNION (...), which turns out to be quicker and only uses INDEX's.

Daniel Vila Boa
  • 670
  • 5
  • 13
0

Mysql can only use the 'left most' part of a composite index. Ie, if your where clause looks like

WHERE A=1 AND B=2

then MySQL can use an index on A or AB, but AB would be best. ACB could not be used as the index is split by another column

With your given WHERE clause, I don't think the MySQL query engine can utilize multiple indexes. I would make an AB and AC and using FORCE INDEX, see which one is faster.

A three column composite key won't help in this case as you are looking up against A and one other column. This sort of key would help if your query was using AND instead of OR. An index on ABC would be useful for

WHERE A=1 AND B=2 AND C=3
sreimer
  • 4,913
  • 2
  • 33
  • 43
  • 2
    Yes, with `AND` it uses the ABC query as expected. Using `A`, `AB`, `AC` would be redundant as mentioned by @Daan in the comment before. From MySQL documentation: "Any leftmost prefix of the index can be used by the optimizer to find rows. `INDEX` on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3)." – Daniel Vila Boa Mar 22 '12 at 21:36
  • 2
    This is a question about OR, not AND. – Chris Moschini Mar 03 '16 at 19:42