Paul D.
Paul D.

Reputation: 157

What would be the best compound index for this MySQL query?

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

Answers (1)

Rick James
Rick James

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

Related Questions