CODEWITHSUNDEEP
phpmysqlsql
Somdeb
Somdeb

Reputation: 87

Mysql query taking too much time

I am using a query in my sql. First i used 

SELECT
    FROM_UNIXTIME(`points`.`timestamp`, '%Y-%m-%d') AS `date`,
    (SELECT  COALESCE(SUM(`points`.`votes`),0) FROM `points`
     WHERE `type` = 1 AND `server_id` = 24 AND
           FROM_UNIXTIME(`points`.`timestamp`, '%Y-%m-%d') = `date`) AS `votes_count`
FROM `points`
WHERE `points`.`timestamp` > UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) AND
      `server_id` = 24
GROUP BY `date`
ORDER BY `date`";

It is too slow taking around 30 sec. My table is enter image description here

What can I do make it faster than 2 sec

Upvotes: 1

Views: 170

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

Try the following query:

SELECT
    FROM_UNIXTIME(`timestamp`, '%Y-%m-%d') AS `date`,
    SUM (CASE WHEN type = 1 THEN votes ELSE 0 END) AS votes_count
FROM points
WHERE `timestamp` > UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) AND
      server_id = 24
GROUP BY FROM_UNIXTIME(`timestamp`, '%Y-%m-%d')
ORDER BY `date`

Here I removed the unnecessary correlated subquery in your SELECT statement. It appears that you just want to conditionally sum votes when the type is 1. If so, then use conditional aggregation in the form of a CASE statement.

If this change doesn't get you fast enough, then consider adding indices on the timestamp and server_id columns. Adding such indices should allow the WHERE clause to execute more efficiently.

As a final note, please avoid using keywords for naming your columns, e.g. date and timestamp. While they are not reserved keywords, they still have a special meaning in the context of, e.g., creating a new table.

Upvotes: 1

Related Questions