[Solved] innodb query taking very long, but appears to be done correctly

How to optimize this SQL query?

In case you have your own slow SQL query, you can optimize it automatically here.

For the query above, the following recommendations will be helpful as part of the SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. 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.
  2. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
  3. Replace Left Join With Subquery (modified query below): The pattern of inflating the amount of data (using joins) and deflating (using GROUP BY) usually slows down queries. In this case, it can be avoided by moving some of the logic to the SELECT clause, and therefore removing some of the LEFT JOINs. In some cases, this transformation can lead to an obsolete GROUP BY clause, which can also be removed.
Optimal indexes for this query:
ALTER TABLE `computers` ADD INDEX `computers_idx_account_id_status` (`account_id`,`status`);
ALTER TABLE `log1` ADD INDEX `log1_idx_user_id` (`user_id`);
ALTER TABLE `log2` ADD INDEX `log2_idx_user_id` (`user_id`);
ALTER TABLE `log3` ADD INDEX `log3_idx_user_id` (`user_id`);
ALTER TABLE `log4` ADD INDEX `log4_idx_user_id` (`user_id`);
ALTER TABLE `log5` ADD INDEX `log5_idx_user_id` (`user_id`);
ALTER TABLE `realtime_logs` ADD INDEX `realtime_logs_idx_event_title_user_id` (`event_title`,`user_id`);
ALTER TABLE `users` ADD INDEX `users_idx_computer_id_user_id` (`computer_id`,`user_id`);
The optimized query:
SELECT
        users.user_id AS DT_RowId,
        users.username AS username,
        computers.computer_name AS computer_name,
        (SELECT
            count(DISTINCT log1.activity_id) AS log1s 
        FROM
            log1 
        WHERE
            log1.user_id = users.user_id LIMIT 1) AS log1s,
        (SELECT
            count(DISTINCT log2.activity_id) AS log2s 
        FROM
            log2 
        WHERE
            log2.user_id = users.user_id LIMIT 1) AS log2s,
        (SELECT
            count(DISTINCT log3.activity_id) AS log3s 
        FROM
            log3 
        WHERE
            log3.user_id = users.user_id LIMIT 1) AS log3s,
        (SELECT
            count(DISTINCT log4.activity_id) AS log4s 
        FROM
            log4 
        WHERE
            log4.user_id = users.user_id LIMIT 1) AS log4s,
        (SELECT
            count(DISTINCT log5.activity_id) AS log5s 
        FROM
            log5 
        WHERE
            log5.user_id = users.user_id LIMIT 1) AS log5s,
        (SELECT
            count(DISTINCT log6.activity_id) AS log6s 
        FROM
            realtime_logs AS log6 
        WHERE
            log6.user_id = users.user_id 
            AND log6.event_title = 'test1' LIMIT 1) AS log6s,
        (SELECT
            count(DISTINCT log7.activity_id) AS log7s 
        FROM
            realtime_logs AS log7 
        WHERE
            log7.user_id = users.user_id 
            AND log7.event_title = 'test2' LIMIT 1) AS log7s,
        (SELECT
            count(DISTINCT log8.activity_id) AS log8s 
        FROM
            realtime_logs AS log8 
        WHERE
            log8.user_id = users.user_id 
            AND log8.event_title = 'test3' LIMIT 1) AS log8s,
        (SELECT
            count(DISTINCT log9.activity_id) AS log9s 
        FROM
            realtime_logs AS log9 
        WHERE
            log9.user_id = users.user_id 
            AND log9.event_title = 'test4' LIMIT 1) AS log9s,
        (SELECT
            count(DISTINCT log10.activity_id) AS log10s 
        FROM
            realtime_logs AS log10 
        WHERE
            log10.user_id = users.user_id 
            AND log10.event_title = 'test5' LIMIT 1) AS log10s,
        (SELECT
            count(DISTINCT log11.activity_id) AS log11s 
        FROM
            realtime_logs AS log11 
        WHERE
            log11.user_id = users.user_id 
            AND log11.event_title = 'test6' LIMIT 1) AS log11s 
    FROM
        computers 
    INNER JOIN
        users 
            ON users.computer_id = computers.computer_id 
    WHERE
        computers.account_id = :cw_account_id 
        AND computers.status = :cw_status 
    GROUP BY
        users.user_id 
    ORDER BY
        NULL

Related Articles



* original question posted on StackOverflow here.