CODEWITHSUNDEEP
performancemariadbsql-order-by
Carl Shiles
Carl Shiles

Reputation: 444

Optimizing Left Join With Group By and Order By (MariaDb)

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;

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.

Upvotes: 0

Views: 804

Answers (3)

Riza
Riza

Reputation: 1184

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.

Upvotes: 1

Alex Weitzer
Alex Weitzer

Reputation: 181

I suspect it's not actually the ordering that is causing the problem... If you remove the limit, I bet the ordered and un-ordered versions will end up performing pretty close to the same.

Depending on if your actual query is as simple as the one you posted, you may be able to get good performance in a single query by using RowNum() as described here:

SELECT u.id, u.display_name, u.cell_phone, u.email, 
uv.year, uv.make, uv.model, uv.id AS user_vehicle_id
FROM (
    SELECT iu.id, iu.display_name, iu.cell_phone, iu.email
    FROM users iu 
    WHERE iu.is_deleted = 0
    ORDER BY iu.display_name) as u
LEFT JOIN user_vehicles uv ON uv.user_id = u.id AND uv.current_owner=1
WHERE ROWNUM() < 10
GROUP BY u.id
ORDER BY u.display_name

If that doesn't work, you probably need to select the users in one select and then select their vehicles in a second Select

Upvotes: 0

jim
jim

Reputation: 278

with a as (
   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
) 
select * from a
ORDER BY u.display_name; 



)

Upvotes: 0

Related Questions