I have a table like this:
id | person_id | created_at --------------------------- 0 | 10 | ... 1 | 10 | ... 2 | 11 | ... 3 | 11 | ... .. | ... | ...
and I'm currently performing the following query:
SELECT * FROM table WHERE person_id IN (10,11,12,34,58) ORDER BY created_at DESC LIMIT x OFFSET y;
I basically want the records sorted by
created_at, but only the ones corresponding to any of the provided
Right I have two separate indices:
person_id, and I've been asking myself the following:
(created_at, person_id)? I'm trying to visualize how it would work, and I think it will still do a sequential scan (i.e. it has the data sorted by `created_at, and it will go record by record collecting the ones that match)
If my query would be
WHERE person_id = 10 instead of
IN, I'm sure the
(person_id, created_at) would do the trick, but I'm not 100% sure in this scenario.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `table` ADD INDEX `table_idx_person_id_created_at` (`person_id`,`created_at`); ALTER TABLE `table` ADD INDEX `table_idx_created_at` (`created_at`);
SELECT * FROM table WHERE table.person_id IN ( 10, 11, 12, 34, 58 ) ORDER BY table.created_at DESC LIMIT x OFFSET y