I am attempting to optimize a query in MariaDb that is really bogged down by its ORDER BY clause. I can run it in under a tenth of a second without the ORDER BY clause, but it takes over 25 seconds with it. Here is the gist of the query:
SELECT u.id, u.display_name, u.cell_phone, u.email, uv.year, uv.make, uv.model, uv.id AS user_vehicle_id FROM users u LEFT JOIN user_vehicles uv ON uv.user_id = u.id AND uv.current_owner=1 WHERE u.is_deleted = 0 GROUP BY u.id ORDER BY u.display_name LIMIT 0, 10;
- I need it to be a left join because I want to include users that aren’t linked to a vehicle.
- I need the group by because I want only 1 result per user (and display_name is not guaranteed to be unique).
- users table has about 130K rows, while user_vehicles has about 230K rows.
Here is the EXPLAIN of the query:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE u index dms_cust_idx PRIMARY 4 null 124825 Using where; Using temporary; Using filesort 1 SIMPLE uv ref user_idx user_idx 4 awscheduler.u.id 1 Using where
I have tried these two indices to speed things up, but they don’t seem to do much.
CREATE INDEX idx_display_speedy ON users(display_name); CREATE INDEX idx_display_speedy2 ON users(id, display_name, is_deleted, dms_cust_id);
I am looking for ideas on how to speed this up. I attempted using nested queries, but since the order by is the bottleneck & order within the nested query is ignored, I believe that attempt was in vain.
WITH a AS ( SELECT u.id, u.display_name, u.cell_phone, u.email FROM users u WHERE u.is_deleted = 0 GROUP BY u.id LIMIT 0, 10 ) SELECT a.id, a.display_name, a.cell_phone, a.email, uv.year, uv.make, uv.model, uv.id AS user_vehicle_id FROM a LEFT JOIN user_vehicles uv ON uv.user_id = a.id AND uv.current_owner=1 ORDER BY a.display_name;
The intention is we take a subset of users before joining it with
Disclaimer: I haven’t verified if its faster or not, but have similar experience in the past where this helps.
Answered By – Riza
Answer Checked By – Robin (BugsFixing Admin)