[SOLVED] Optimizing Left Join With Group By and Order By (MariaDb)

Issue

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.

Solution

how about:

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 user_vehicles.
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)

Leave a Reply

Your email address will not be published. Required fields are marked *