Equality Range Query in MySQL runs very slowly

Question

Table event (id, source_id, start_time), 140k records
Index (source_id asc, start_time desc)

My query:

SELECT * FROM event where source_id in (4, 5, 3, 10)
order by start_time desc limit 100;

Run time ~ 2 seconds.

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	event	null	ALL	idx_source_start_time	null	null	null	125504	100	Using where; Using filesort

Currently, my table only has event of source_id 3, no event of source 4, 5, 10. With index (camera_id, start_time), I think that MySQL will dive into BTree and know source 4, 5, 10 have no matched records. But this query runs very slow. How can I optimize this query ?

Answer 1

If your MSQL version is at least 8.0.14 you can use VALUES to construct a list of sources_id, then use a LATERAL join to force the extraction of 100 events for each source_id and then order by start_time to get the latest 100 overall.

SELECT filtered_events.* FROM 
  (VALUES ROW(4),ROW(5),ROW(3),ROW(10)) AS sources(id)
JOIN LATERAL 
  (SELECT * FROM event WHERE event.source_id = sources.id 
   ORDER BY source_id asc, start_time desc LIMIT 100) AS filtered_events 
ORDER BY start_time desc LIMIT 100;

The filtered_events subquery should then use your Index (source_id asc, start_time desc)

Answer 2

I would put in an index on start_time, source_id. Since you are ordering by start_time and filtering by source_id.

Answer 3

( SELECT * FROM event WHERE source_id = 4  ORDER BY start_time DESC  LIMIT 100 )
UNION ALL
( SELECT * FROM event WHERE source_id = 5  ORDER BY start_time DESC  LIMIT 100 )
UNION ALL
( SELECT * FROM event WHERE source_id = 3  ORDER BY start_time DESC  LIMIT 100 )
UNION ALL
( SELECT * FROM event WHERE source_id = 10 ORDER BY start_time DESC  LIMIT 100 )
)  ORDER BY start_time DESC  LIMIT 100;

Yes, it depends on INDEX (source_id, start_time), but note that ASC and DESC don't matter in the index. Each SELECT will optimally use the index; the final ORDER BY will need to sort only 4*100 rows.

(This works if you don't have a version with LATERAL.)

If you need to paginate, let me know; it gets a bit tricky.

Performance will depend on data distribution, the LIMIT value, the number of source_ids selected, and the phase of the moon.