CODEWITHSUNDEEP
mysqlsqlperformancequery-optimization
O Connor
O Connor

Reputation: 4412

How to speed up the SQL query execution time in MySQL database?

Below is my SQL query.

SELECT `left_table`.`right_table_id`, MAX(left_table.add_time) AS max_add_time 
    FROM `left_table` 
    LEFT JOIN `right_table` ON `left_table`.`right_table_id` = `right_table`.`id` 
    WHERE left_table.add_time <= NOW()
        AND (
                (right_table.some_id = 1 AND right_table.category != -2)
                OR
                (right_table.another_id = 1 AND right_table.category != -1)
        ) AND NOT(right_table.category = -3) 
        AND NOT(right_table.category = -4) 
    GROUP BY `right_table_id` 
    ORDER BY `max_add_time` DESC, `left_table`.`id` DESC
LIMIT 12

It takes 5356.6ms to execute this query. It takes too long to me. I have been trying and trying to speed up the execution time. But no result. How can I improve the execution time for the above query?

Upvotes: 0

Views: 511

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271211

Hmmm . . . I would start by writing the logic like this:

SELECT COUNT(DISTINCT lt.`right_table_id`)
FROM `left_table` lt LEFT JOIN
     `right_table` rt
      ON lt.`right_table_id` = rt.`id` 
WHERE lt.add_time <= NOW() AND
      ((rt.some_id = 1 AND rt.category <> -2) OR
       (rt.another_id = 1 AND rt.category <> -1)
      ) AND
      rt.category NOT IN (-3, -4);

There might be additional simplifications, depending on whether lt.right_table_id always matches a row in the right table (or is NULL). And various other considerations.

Upvotes: 1

Related Questions