MySQL Query Performance – Equality Range Query Runs Slowly

MySQLquery-performance

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 ?

Best Answer

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)

Related Solutions

Mysql – EXPLAIN output suggests that the index is not being used

You misunderstand what the 'rows' field is. It is the number of rows that mysql estimates it shall need to read to satisfy your query. This value can be quite inaccurate. It does not mean this is the number of rows in the result - or the actual number of rows read by mysql

MySQL Performance – Query Takes Forever After Adding WHERE Condition

For the first query:

SELECT 
    *
FROM
    xercasehistory
WHERE
    xerId = 192             
ORDER BY 
    id DESC 
LIMIT 10;

you need an index on (xerId, id) - or just on (xerId) if the table is InnoDB. Four seconds is a very long time for a query that is so simple and returns no more than 10 rows. Add this index and try again. Query time should drop to a few milliseconds.

For the second query:

SELECT 
    *
FROM
    xercasehistory
WHERE
        xerId = 192 
    AND type = 'case'
    AND fieldName = 'statusCode'
    AND typesKey = 5            
ORDER BY 
    id DESC 
LIMIT 10 ;

the same index would be useful. If efficiency is not superb (a few milliseconds) for this query with the same index, you may have many (thousands) rows that match the index (xerId = 192) but not so many that match all 4 conditions, so a more appropriate index (on all 4 columns + the id) would be more efficient.

As for:

We can see an index in all fields used in where clause (xerId, type, type, typesKey) ...

No, there isn't a composite index. Theer are 4 indexes, on each column separately.

Why the (xerId) index is not used, at least for the first query is weird though. I would replace it with an index on (xerId, Id) and check the EXPLAIN output after that.

Best Answer

Related Solutions

Mysql – EXPLAIN output suggests that the index is not being used

MySQL Performance – Query Takes Forever After Adding WHERE Condition

Related Question