Optimize MySQL Query using indexes (what index?)

Question

I am struggling to optimize the following query which takes an average of 2000 ms.

select  count(pk)
    from  mytable
    where  (pk<>5
      and  url='test.png'
      and  (data=124578 or  (data is null and  pk=1234578)))
      and  type in (123,456,789,015,789) 

Here are some info :

select count(*) from mytable

1 526 588 lines

show indexes in mytable

Table   non_unique  key_name            seq_in_index    column_name collation   cardinality
mytable 0           PRIMARY             1               PK          A           1405079     
mytable 1           data                1               data        A           1405079     
mytable 1           Media_Code_30       1               code        A           1405079     
mytable 1           codeVersionIDX_30   1               code        A           1405079     

Explain :

id  select_type table   type        possible_keys   key     key_len ref     rows    extra
1   SIMPLE      mytable ref_or_null PRIMARY,data    data    9       const   635908  Using where

I really have no idea whether this is optimized enough, or it can be better by creating new indexes (or composite indexes).

The query, however, cannot be changed as it is coming from another system on which I do not have my hands on!

Answer 1

The best index for that query is

INDEX(url, type) -- in that order

Indexes should start with any columns that are "= constant" (url in this case), then move on to one more column that is more complex (type IN ...). See my cookbook.

Answer 2

This is not the Answer. Only some sample for using index

SAMPLE

small Table

MariaDB [bb]> SHOW CREATE TABLE useindex\G
*************************** 1. row ***************************
       Table: useindex
Create Table: CREATE TABLE `useindex` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `num1` int(11) DEFAULT NULL,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2031586 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

number of rows and sample data

MariaDB [bb]> SELECT count(*) FROM USEindex;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.43 sec)

MariaDB [bb]> SELECT * FROM useindex LIMIT 10;
+----+------+------+
| id | num1 | num2 |
+----+------+------+
|  1 |  405 |  906 |
|  2 |  656 |  656 |
|  3 |  906 |  407 |
|  4 |  156 |  157 |
|  5 |  406 |  908 |
|  6 |  656 |  659 |
|  7 |  907 |  409 |
|  8 |  157 |  160 |
|  9 |  407 |  910 |
| 10 |  657 |  661 |
+----+------+------+
10 rows in set (0.00 sec)

EXPLAIN 3 queries (no index)

MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500;
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | useindex | ALL  | NULL          | NULL | NULL    | NULL | 1996444 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num2 = 600;
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | useindex | ALL  | NULL          | NULL | NULL    | NULL | 1996444 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500 AND num2 = 600;
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | useindex | ALL  | NULL          | NULL | NULL    | NULL | 1996444 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

add 2 indexe

MariaDB [bb]> ALTER TABLE useindex
    -> ADD KEY n1 (num1),
    -> ADD KEY n2 (num2);
Query OK, 0 rows affected (12.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

EXPLAIN with index

see if the WHERE with 2 fields, MySQL use only one INDEX

MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
|    1 | SIMPLE      | useindex | ref  | n1            | n1   | 5       | const | 2003 |       |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num2 = 600;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
|    1 | SIMPLE      | useindex | ref  | n2            | n2   | 5       | const | 1993 |       |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500 AND num2 = 600;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
|    1 | SIMPLE      | useindex | ref  | n1,n2         | n2   | 5       | const | 1993 | Using where |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

add COMPOSITE Index over both fields

MariaDB [bb]> ALTER TABLE useindex
    -> ADD KEY n12 (num1,num2);
Query OK, 0 rows affected (7.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

EXPLAIN Queries again

Now they use the COMOSITE Index

MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
|    1 | SIMPLE      | useindex | ref  | n1,n12        | n12  | 5       | const | 2003 | Using index |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num2 = 600;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
|    1 | SIMPLE      | useindex | ref  | n2            | n2   | 5       | const | 1993 |       |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500 AND num2 = 600;
+------+-------------+----------+------+---------------+------+---------+-------------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+-------------+------+-------------+
|    1 | SIMPLE      | useindex | ref  | n1,n2,n12     | n12  | 10      | const,const |    1 | Using index |
+------+-------------+----------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

Tell the OPTIMIZER to MERGE INDEX

MariaDB [bb]> SET optimizer_switch='index_merge=on';
Query OK, 0 rows affected (0.00 sec)

drop INDEX n12

MariaDB [bb]> alter table useindex drop key n12;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

RUN one QUERY with MERGE

MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500 AND num2 = 600;
+------+-------------+----------+-------------+---------------+-------+---------+------+------+--------------------------------------------------+
| id   | select_type | table    | type        | possible_keys | key   | key_len | ref  | rows | Extra                                            |
+------+-------------+----------+-------------+---------------+-------+---------+------+------+--------------------------------------------------+
|    1 | SIMPLE      | useindex | index_merge | n1,n2         | n2,n1 | 5,5     | NULL |    1 | Using intersect(n2,n1); Using where; Using index |
+------+-------------+----------+-------------+---------------+-------+---------+------+------+--------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [bb]>

Answer 3

MySQL can only use one INDEX in a query.

so it is necessary to have composite index. the best way for the order of the fields in the index is the field which reduce the resultset mostly and so on.

lets say you have a table with names and birthday and you have many duplicates of names and only a few of birthday. so then create the index like (birthday,name) the mysql search first the records with the searched day and then only in this little result the name

Answer 4

Hmmm, this query seems difficult to set up an index for:

select count(pk)
from mytable
where (pk <> 5 and url = 'test.png' and
       (data = 124578 or (data is null and pk = 1234578))
      ) and
      type in (123, 456, 789, 015, 789);

My suggestion is to start with type and include the other columns in the index: mytable(type, url, data, pk). This is a covering index and that might improve performance a bit.