0

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!

Improve this question
1
  • Assuming pk is the PRIMARY KEY, hence never NULL, there is no need for COUNT(pk) instead of COUNT(*). Commented May 26, 2016 at 22:46

4 Answers 4

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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]>
Improve this answer

5 Comments

If MySQL uses only one index ever, how come that your final query shows that it merged information from multiple indexes in order to find required row?
YES, they MERGE!! 2 indexe, not use 2
But it uses two indexes to come up with the best path to retrieve the data, it's not like it used only one or another but both. You wrote that it only uses one index ever, and this snippet proves that it uses multiple indexes by merging them, since there's no other conceptual way to do it anyway. Therefore, MySQL is able to use multiple indexes per query. If MySQL only used one index per query, what would be the point of using MySQL if it's so poor with retrieval strategy (and we know it isn't poor with retrieval strategy)?
Yes, "index merge" will sometimes use two indexes. But it is almost always not as efficient as a 'composite index'. More on composite indexes.
Upvote for nice explanation and demo of various things.
0

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

Improve this answer

17 Comments

Since when can MySQL use only one index in a query? Any docs to back that up? I'd like to believe you.
always, 20 years, i am not talking about subqueries. take a query a show what EPLAIN says. The OPTIMIZER looks for available Indexes und use the best one or none, or update me
So there's no document that states "MySQL uses only one index per query" or did I misunderstood you while you meant "In the query that OP posted, MySQL can use only one index"?
sorry, i can quick find a document with this information, but i also not found that mysql can use more. but i will search later gain and post you a link
Actually using two indexes is so rare as to be not worth considering. The only way that MySQL uses two indexes for a single SELECT involves fetching ids from each of two indexes, then 'anding' ('intersect') or 'oring' ('union') the lists in some temp space. All of this is bulky and slow, hence rarely improves performance over simply using one index, then filtering on the WHERE or (in the case of JOIN, using Nested Loop Join).
|
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.