10

Here is my table with ~10,000,000 rows data

CREATE TABLE `votes` (
  `subject_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `subject_id` int(11) NOT NULL,
  `voter_id` int(11) NOT NULL,
  `rate` int(11) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`subject_name`,`subject_id`,`voter_id`),
  KEY `IDX_518B7ACFEBB4B8AD` (`voter_id`),
  KEY `subject_timestamp` (`subject_name`,`subject_id`,`updated_at`),
  KEY `voter_timestamp` (`voter_id`,`updated_at`),
  CONSTRAINT `FK_518B7ACFEBB4B8AD` FOREIGN KEY (`voter_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Here is the indexes cardinalities

enter image description here

So when I do this query:

SELECT SQL_NO_CACHE * FROM votes WHERE 
    voter_id = 1099 AND 
    rate = 1 AND 
    subject_name = 'medium'
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100;

I was expecting it uses index voter_timestamp but mysql chooses to use this instead:

explain select SQL_NO_CACHE * from votes  where subject_name = 'medium' and voter_id = 1001 and rate = 1 order by updated_at desc limit 20 offset 100;`

type:
    index_merge
possible_keys: 
    PRIMARY,IDX_518B7ACFEBB4B8AD,subject_timestamp,voter_timestamp
key:
    IDX_518B7ACFEBB4B8AD,PRIMARY
key_len:
    102,98
ref:
    NULL
rows:
    9255
filtered:
    10.00
Extra:
    Using intersect(IDX_518B7ACFEBB4B8AD,PRIMARY); Using where; Using filesort

And I got 200-400ms query time.

If I force it to use the right index like:

SELECT SQL_NO_CACHE * FROM votes USE INDEX (voter_timestamp) WHERE 
    voter_id = 1099 AND 
    rate = 1 AND 
    subject_name = 'medium'
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100;

Mysql can return the results in 1-2ms

and here is the explain:

type:
    ref
possible_keys:
    voter_timestamp
key:
    voter_timestamp
key_len:
    4
ref:
    const
rows:
    18714
filtered:
    1.00
Extra:
    Using where

So why didn't mysql choose the voter_timestamp index for my original query?

What I had tried is analyze table votes, optimize table votes, drop that index and add it again, but mysql still uses the wrong index. not quite understand what is the problem.

6
  • 1
    @ypercubeᵀᴹ I don't think it is necessary to index all columns in the where condition, as you see if I force to use the (voter_id, updated_at) index, it can use it and be very efficient. If I remove the subject_name = "medium" part it also can pick the right index, no need to index rate
    – Phoenix
    Oct 4, 2016 at 7:11
  • Still, the 4-column index will be more efficient than the 2 (voter_id, updated_at). Another index would be (voter_id, subject_name, updated_at) or (subject_name, voter_id, updated_at) (without the rate). Oct 4, 2016 at 8:10
  • 1
    And yes, you are - on some point - right. You don't need the 4-column index. It's just the best possible index for this query. The 2-column (that you think is "right") maybe ok for the data and distribution you currently have. With a different distribution, it might be horrible. Example: Suppose the 99% of the rows had rate>1 and only 1% had rate=1. Do you think using the 2-column index would be efficient? Oct 4, 2016 at 8:12
  • It would have to traverse a large part of the index and do thousands of lookups on the table, only to find that rate>1 and reject the rows, until it finds 120 that fit the criteria that can't be judged by the index (subject_name='medium' and rate=1) Oct 4, 2016 at 8:16
  • ypercube, Phoenix -- MySQL won't get to the LIMIT or even the ORDER BY unless the index first satisfies all the filtering. That is, without the full 4-columns, it will collect all the relevant rows, sort them all, then pick off the LIMIT. With the 4-column index, the query can avoid the sort and stop after reading only the LIMIT rows.
    – Rick James
    Jan 18, 2018 at 15:29

2 Answers 2

7

For that query, you need this index:

INDEX(voter_id, rate, subject_name, updated_at)

The updated_at must be last; the other three can be in any order. (ypercube's 3-column indexes are not very useful since they don't finish off the WHERE columns before hitting the ORDER BY column.)

As you add this index, you can probably get rid of all the other secondary keys:

KEY IDX_518B7ACFEBB4B8AD (voter_id), -- The FK can use my index KEY subject_timestamp (subject_name,subject_id,updated_at), -- mostly redundant KEY voter_timestamp (voter_id,updated_at), -- may have been your attempt

With the 4-column index, you have a chance of optimizing the "pagination" and avoid OFFSET. See this blog.

On another topic... When I see X_name and X_id, I assume "normalization" is going on. I would expect to see those two columns in a table, with virtually nothing else. I would not expect to see both in some other table.

(voter_id, updated_at) won't get past voter_id since it has not finished with filtering (the WHERE). Then, since a the other index is smaller, it is picked. Mine has 3 columns to take care of filtering, then the column for ORDER BY.

0
6

MySQL is using a relatively simple (simpler than other RDBMS) cost model for planning queries in which filtering your dataset has quite high priority. In your first query with the merge index it is estimated that scanning ~9000 rows is going to be necessary while the second with the index hint will require 18000. My bet would be that this weighs in the calculation enough to move the scale towards the merge. You can confirm this (or find other reasons) by turning optimizer_trace on, run your query and evaluate the results.

set global optimizer_trace='enabled=on';

-- run your query 

SELECT SQL_NO_CACHE * FROM votes WHERE 
    voter_id = 1099 AND 
    rate = 1 AND 
    subject_name = 'medium'
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100;

select * from information_schema.`OPTIMIZER_TRACE`;

One remark on index_merge: in most cases you will find that it is quite expensive. Although very useful for OLAP type scenarios it might not be very well suited for OLTP because the operation can take significant time of your query and as you can see sometimes the suboptimal execution plan is actually faster.

Fortunately MySQL provides switches for optimizer so you can customize it as you wish.

For all the option you can run:

show global variables like 'optimizer_switch';

For changing one you don't have to copy paste the whole string. It works such as dict.update() in python.

 set global optimizer_switch='index_merge=off';

If possible I would also take a look at your table structure and improve. Having a ~100 byte primary key with many secondary keys is not really advised.

You have four secondary keys and some of them are superfluous for example (voter_id) index is a subset of (voter_id, updated_at)

1
  • "Index merge intersect" is rarely used by MySQL. In perhaps all cases, it is significantly better to have an index with more columns. "Index merge union" is sometimes useful; turning OR into UNION is often as good or better.
    – Rick James
    Jan 18, 2018 at 15:25

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.