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
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.
subject_name = "medium"
part it also can pick the right index, no need to indexrate
(voter_id, updated_at)
. Another index would be(voter_id, subject_name, updated_at)
or(subject_name, voter_id, updated_at)
(without the rate).subject_name='medium' and rate=1
)LIMIT
or even theORDER 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 theLIMIT
. With the 4-column index, the query can avoid the sort and stop after reading only theLIMIT
rows.