1

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 ?

7
  • I think that MySQL will dive into BTree and know source 4, 5, 10 have no matched records. No. MySQL can detect this only during the query execution, so this cannot be taken into account during the execution plan building.
    – Akina
    Mar 16, 2023 at 6:12
  • How can I optimize this query ? Select using where source_id = X, 100 rows per value (Index should work), then UNION ALL and one more ORDER BY + LIMIT.
    – Akina
    Mar 16, 2023 at 6:14
  • @Akina Using union, run time is 120ms. However, my query can have up to 50 sources. I think MySQL must do this UNION internally for query IN (...).
    – kietheros
    Mar 16, 2023 at 7:34
  • I think MySQL must MySQL does not make any assumptions. It uses only available facts. For example, the fact about the data (your words about "source 4, 5, 10 have no matched records") which can be taken from the table statistic is approximate, so it won't be taken into consideration.
    – Akina
    Mar 16, 2023 at 7:59
  • @Akina I mean, with index (source, start_time), MySQL can fast retrieve records of each sources and statistics data. And it only should get 100 results of each sources then run union + sort. This query should be faster. Writing SQL with 50 union is complex (and ugly).
    – kietheros
    Mar 16, 2023 at 9:33

3 Answers 3

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)

3
  • This query works fine. But I have to use raw query rather than using ORM. Thanks !
    – kietheros
    Mar 17, 2023 at 6:41
  • 1
    @shang12 - ORMs are not designed for performance.
    – Rick James
    Apr 1, 2023 at 0:20
  • In a similar test, this showed that my UNION approach may be faster than LATERAL: SHOW SESSION STATUS LIKE 'Handler%';
    – Rick James
    Apr 1, 2023 at 0:22
0

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

3
  • This will cause index scan (not index seek), and anycase this should use final filesort.
    – Akina
    Mar 16, 2023 at 6:15
  • @Akina Using this index (start_time, source_id), query time is 100 ms. It will scan index, and not efficient if events of a filtered source only have at the end of BTree (not test). However, with current event data, it works fine.
    – kietheros
    Mar 16, 2023 at 8:43
  • I have just tested again. Index (start_time, source_id) only efficient for filtering list of sources which contain source_id 3. The other queries run slowly.
    – kietheros
    Mar 16, 2023 at 9:01
0
( 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.

1
  • Your query works, but if there are 50 source ids, this query looks a bit long and ugly. However, I can use ORM for writing this union query.
    – kietheros
    Apr 2, 2023 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.