Reputation: 157
What would be the best compound index for this MySQL query?
SELECT
c.id, c.customer_id, c.service_id, c.origin_id, c.title, c.state, c.start_date_time
FROM
calendar_events c
WHERE
c.customer_id = 1234
AND c.state IN ('unconfirmed', 'confirmed')
AND c.start_datetime BETWEEN '2016-10-15 00:00:00' AND '2016-10-15 23:59:59';
Upvotes: 0
Views: 32
Reputation: 142258
Probably this:
INDEX(customer_id, -- '=' comes first
state, -- 'IN' sometimes works ok in the middle
start_datetime) -- nothing after a 'range' will be used
Run EXPLAIN SELECT ...
It will probably say "MRR" in the Other
column. I call this the 'leapfrog' optimization. In your case it will easily reach into the start of index rows with 1234 & 'unconfirmed' & 00:00. Then it will range scan consecutive entries for that day.
Then it will leap over to 1234 & 'confirmed' & 00:00 for another scan.
That adds to two BTree probes into the index, plus two index scans. The is very efficient.
Then, for each row in the index, it will reach over (using the PRIMARY KEY
) to get the other columns needed.
Recommended pattern for date&time range:
AND c.start_datetime >= '2016-10-15'
AND c.start_datetime < '2016-10-15' + INTERVAL 1 DAY
Upvotes: 1