MYSQL slow ORDER BY

Question

I have an issue when adding an ORDER BY on my query. without ORDER BY query takes around 26ms, as soon as I add a ORDER BY it's taking around 20s.

I have tried a few different ways but can seem to reduce the time.

Tried

FORCE INDEX (PRIMARY)

Tried wrapping

SELECT * FROM (...)

Does anyone have any other ideas?

QUERY:

    SELECT 
    orders.id AS order_number, 
    orders.created AS order_created, 
    CONCAT_WS(' ', clients.name, office.name) AS client_office, 
    order_item.code AS product_code, 
    order_item.name AS product_name, 
    order_item.description AS product_description, 
    order_item.sale_price, 
    jobs.rating AS job_rating, 
    job_role.start_booking AS book_start, 
    job_role.end_booking AS book_end, 
    CONCAT_WS(' ', admin_staffs.first_name, admin_staffs.last_name) AS soul, 
    admin_roles.name AS role, 
    services.name AS service, 
    COUNT(job_item.id) AS total_assets, 
    COALESCE(orders.project_name, CONCAT_WS(' ', orders.location_unit_no, orders.location_street_number, orders.location_street_number, orders.location_street_name
                )
            ) AS order_title
FROM jobs
LEFT JOIN job_item ON jobs.id = job_item.job_id
LEFT JOIN job_role ON jobs.id = job_role.job_id
LEFT JOIN admin_roles ON admin_roles.id = job_role.role_id
LEFT JOIN services ON services.id = jobs.service_id
LEFT JOIN admin_staffs ON admin_staffs.id = job_role.staff_id
LEFT JOIN order_item ON jobs.item_id = order_item.id
LEFT JOIN orders ON orders.id = order_item.order_id
LEFT JOIN clients ON orders.client_id = clients.id
LEFT JOIN office ON orders.office_id = office.id
LEFT JOIN client_users ON orders.user_id = client_users.id
GROUP BY jobs.id
ORDER BY jobs.order_id DESC
LIMIT 50

EXPLAIN:


+----+-------------+--------------+--------+----------------+-----------+---------+--------------------------------------+-------+---------------------------------+
| id | select_type | table        | type   | possible_keys  | key       | key_len | ref                                  | rows  | Extra                           |
+----+-------------+--------------+--------+----------------+-----------+---------+--------------------------------------+-------+---------------------------------+
| 1  |    SIMPLE   | jobs         | ALL    | PRIMARY        | NULL      | NULL    | NULL                                 | 49555 | Using temporary; Using filesort |        
| 1  |    SIMPLE   | job_item     | ref    | job_id         | job_id    | 5       | Wx3392vf_UCO_app.jobs.id             | 8     | Using where; Using index        |
| 1  |    SIMPLE   | job_role     | ref    | job_id         | job_id    | 4       | Wx3392vf_UCO_app.jobs.id             | 1     | Using where                     |
| 1  |    SIMPLE   | admin_roles  | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.job_role.role_id    | 1     | Using where                     |
| 1  |    SIMPLE   | services     | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.jobs.service_id     | 1     | Using where                     |
| 1  |    SIMPLE   | admin_staffs | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.job_role.staff_id   | 1     | NULL                            |    
| 1  |    SIMPLE   | order_item   | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.jobs.item_id        | 1     | Using where                     |
| 1  |    SIMPLE   | orders       | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.order_item.order_id | 1     | Using where                     |
| 1  |    SIMPLE   | clients      | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.orders.client_id    | 1     | Using where                     |
| 1  |    SIMPLE   | office       | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.orders.office_id    | 1     | Using where                     |
| 1  |    SIMPLE   | client_users | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.orders.user_id      | 1     | Using index                     |
+----+-------------+--------------+--------+----------------+-----------+---------+--------------------------------------+-------+---------------------------------+

Why are you using GROUP BY when you have no aggregates or anything? This will most likely produce unstable results and while MySQL allows it it shouldn’t be done. Also sounds strange that id could have duplicates, usually it’s a unique column. The query will take longer with order since without it it can just return any 50 rows, with it all rows have to be handled. — Sami Kuhmonen
The GROUP BY is for the COUNT, without it will count all records in job_item and return one result — Ranga
I would suggest turning the server’s settings so that it will produce an error for not using aggregates etc when grouping and fix the query based on those. It might not help with this issue, but it will help when the query is at least determinate. — Sami Kuhmonen
If you remove all joins from your query and try to fetch just one column from jobs table and make order by. How fast will it be? I think you should use this strategy and try to figure out where is the bottleneck. — Jacobian

Jacobian Jacobian · Accepted Answer · 2018-03-21T07:05:02

Try to start from this query:

SELECT * FROM jobs ORDER BY jobs.order_id DESC LIMIT 50

And then increase its complexity by adding more joins:

SELECT * FROM jobs 
LEFT JOIN job_item ON jobs.id = job_item.job_id
ORDER BY jobs.order_id DESC LIMIT 50

etc.

Make profiling at each step and you will be able to find a bottleneck. Probably, it is some TEXT field in one of joined tables that slows down the query or some other reason.

MYSQL slow ORDER BY

1 Answers