CODEWITHSUNDEEP
mysqlindexing
Michele Mariotti
Michele Mariotti

Reputation: 7459

MySQL: define the right indexes

I have this table:

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| ID                  | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| CODE                | varchar(255) | NO   |     | NULL    |                |
| REVISION            | varchar(255) | NO   |     | NULL    |                |
| NAME                | varchar(255) | NO   |     | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------+

now, my app will perform these queries (from more likely to less likely):

  1. SELECT * FROM ITEM WHERE ID = ?
  2. SELECT * FROM ITEM WHERE CODE = ? ORDER BY REVISION DESC
  3. SELECT * FROM ITEM WHERE CODE = ? AND REVISION = ?
  4. SELECT * FROM ITEM WHERE CODE LIKE ? OR NAME LIKE ? ORDER BY CODE ASC, REVISION DESC

What is the best combination of indexes for such a scenario?

I'm using MySQL 5.6.14 (InnoDB) on Windows 8.1 x64.

UPDATE (still testing):

The best so far is using separate indexes: IDX_CODE (CODE), IDX_NAME (NAME).

mysql> describe select id, code, revision from UNIT where code like 'M0170SIGM1%' or name like 'M0170SIGM1%' ORDER BY code ASC, revision DESC;
+----+-------------+-------+-------------+-------------------+-------------------+---------+------+------+------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys     | key               | key_len | ref  | rows | Extra                                                            |
+----+-------------+-------+-------------+-------------------+-------------------+---------+------+------+------------------------------------------------------------------+
|  1 | SIMPLE      | UNIT  | index_merge | IDX_CODE,IDX_NAME | IDX_CODE,IDX_NAME | 767,767 | NULL |   31 | Using sort_union(IDX_CODE,IDX_NAME); Using where; Using filesort |
+----+-------------+-------+-------------+-------------------+-------------------+---------+------+------+------------------------------------------------------------------+

No way to make MySQL use indexes in double direction sorting.

Upvotes: 0

Views: 87

Answers (2)

Olli
Olli

Reputation: 1738

For your fourth case with the mixed asc/desc order it might fail completely to use an index since mysql seems not to support this combination:

Order-By-Optimization in mysql

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

...

You mix ASC and DESC:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

Possible solution for that problem:

if your revision would be simply an positive integer, you could use another column with that value multiplied by -1, and order by this column DESC, which would allow you to use an index on those two columns, since they would have the same direction... works here like a charm with a timestamp column...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

The primary key takes care of the first query.

For the second and third: item(code, revision). Note that the order by case asc is unnecessary because you are only choosing one code.

The last query is problematic. You might want to use a full text index. If the like attern is does not have a wild card at the beginning, then the following formulation might be better:

select i.*
from (select i.* from item i where code like ? union
      select i.* from item i where name like ?
     ) i
order by code, revision;

However, the engine is probably going to implement this query using full-table scan followed by a sort, regardless of the indexes.

Upvotes: 1

Related Questions