CODEWITHSUNDEEP
phphtmlmysql
Somdeb
Somdeb

Reputation: 87

Database query needs a lot of time

I am currently running this query and it takes around 15 sec to load i want to optimize the query 

SELECT *, `points`.`players` as `players` ,
FROM_UNIXTIME(`points`.`timestamp`, '%Y-%m-%d %H:%i') as `date`,
        (SELECT   `points`.`players`
        FROM `points`
        WHERE   FROM_UNIXTIME(`points`.`timestamp`, '%Y-%m-%d %H:%i') > `date` - INTERVAL 7 DAY 
                AND FROM_UNIXTIME(`points`.`timestamp`, '%Y-%m-%d %H:%i') < `date` - INTERVAL 167 HOUR 
                AND `server_id` = {$server_id} 
                AND `type` = 2 
                LIMIT 1 
        ) as `prevplayers`
FROM `points`
WHERE   `points`.`timestamp` > UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY) 
        AND `server_id` = {$server_id} 
        AND `type`=2 
GROUP BY DATE_FORMAT(FROM_UNIXTIME(`points`.`timestamp`), '%Y-%m-%d %H') 
ORDER BY DATE_FORMAT(FROM_UNIXTIME(`points`.`timestamp`),'%Y-%m-%d %H')

Table structure

enter image description here

Indexesenter image description here

With Explain Outputenter image description here

Ideal time is 1-2 secs

Any Suggestions?

Thanks Somdeb

Upvotes: 1

Views: 73

Answers (1)

dilsingi
dilsingi

Reputation: 2958

Can you create an additional index with the columns in following order and retry the query?

  1. server_id
  2. type
  3. timestamp (mention descending order)

Upvotes: 1

Related Questions