EverSQL Database Performance Knowledge Base

Question 1

EverSQL Database Performance Knowledge Base

group by and order by making query very slow in mysql?

Database type:

This query executes quickly:

SELECT SQL_NO_CACHE users.id, users.name
FROM users 
LEFT JOIN user_statistics ON users.id = user_statistics.user_id
WHERE users.is_deleted = 0
GROUP BY users.id
ORDER BY users.updated_at
LIMIT 50

This one slowly:

SELECT SQL_NO_CACHE users.id, users.name, MAX(user_statistics.access_time) AS user_access_time
FROM users 
LEFT JOIN user_statistics ON users.id = user_statistics.user_id
WHERE users.is_deleted = 0
GROUP BY users.id
ORDER BY user_access_time
LIMIT 50

Here's the explain output for the slow query:

*************************** 1. row ***************************
           id: 1   select_type: SIMPLE
        table: users
         type: ref possible_keys: fk_users_is_deleted
          key: fk_users_is_deleted
      key_len: 4
          ref: const
         rows: 107696
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1   select_type: SIMPLE
        table: user_statistics
         type: ref possible_keys: fk_user_statistics_user_id
          key: fk_user_statistics_user_id
      key_len: 4
          ref: users.id
         rows: 1
        Extra:

I am using GROUP BY and ORDER BY on different tables. How can I optimize the above slow query?

Answer

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Remove Redundant Left Joins (modified query below): Redundant LEFT JOINs (e.g. `user_statistics`) were detected in the query. Removing them will result in a performance improvement. In some cases, JOINs become redundant after an optimization is applied, such as when converting OR conditions to a UNION clause.

Optimal indexes for this query:

ALTER TABLE `users` ADD INDEX `users_idx_is_deleted_id` (`is_deleted`,`id`);

The optimized query:

SELECT
        SQL_NO_CACHE users.id,
        users.name 
    FROM
        users 
    WHERE
        users.is_deleted = 0 
    GROUP BY
        users.id 
    ORDER BY
        users.updated_at LIMIT 50

Question 2

EverSQL Database Performance Knowledge Base

group by and order by making query very slow in mysql?

How to optimize this SQL query?

The optimization process and recommendations:

Optimal indexes for this query:

The optimized query:

Related Articles