CODEWITHSUNDEEP
mysql
stefek143
stefek143

Reputation: 299

speed up mysql query with group by

I have a MySQL query :

SELECT date(FROM_UNIXTIME(time)) as date,  
count(view) as views  
FROM ('table_1')  
WHERE 'address' = 1  
GROUP BY date(FROM_UNIXTIME(time))

where

view : auto increment and primary key, (int(11))
address : index , (int(11))
time : index, (int(11))

total rows number of the table is : 270k

this query have slow executing, in mysql-slow.log I got :

Query_time: 1.839096
Lock_time: 0.000042
Rows_sent: 155
Rows_examined: 286435

with use EXPLAIN looks like below:

id      select_type     table       type    possible_keys   key     key_len     ref     rows        Extra
1       SIMPLE          table_1     ref     address         address 5           const   139138      Using where; Using temporary; Using filesort

How to improve this query to speed up executing? Maybe better will be if I change date in PHP? But I think in PHP take a date as timestamp next convert to human readable and make "group by" will take more time then one query in MySQL.

Maybe somebody knows how to make this query faster?

Upvotes: 1

Views: 662

Answers (2)

Ray
Ray

Reputation: 41428

When you apply the functions date() and FROM_UNIXTIME() to the time in the group by you kill any indexing benefit you may have on that field.

Adding a date column would be the only way i can see speeding this up if you need it grouped by day. Without it, you'll need to decrase the overall set you are trying to group by. You could maybe add start/end dates to limit the date range. That would decrease the dates being transformed and grouped.

Upvotes: 1

Joshua Martell
Joshua Martell

Reputation: 7212

You should consider adding a additional DATE column to your table and indexing it.

Upvotes: 0

Related Questions