0

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.

Carl Shiles
  • 434
  • 3
  • 15
  • There is a trick to order results after the query. The answer already exists, please check [Slow query when using order by](https://stackoverflow.com/questions/884661/slow-query-when-using-order-by#answer-884678). – skobaljic Feb 08 '22 at 16:33

3 Answers3

1

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.

Riza
  • 1,144
  • 8
  • 19
0
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; 



)
jim
  • 228
  • 4
  • 10
  • 1
    I ran this after changing the ORDER BY to 'ORDER BY a.display_name' and it did not speed things up significantly. – Carl Shiles Jan 28 '22 at 22:31
0

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

Alex Weitzer
  • 181
  • 1
  • 12