0

I have a query like this:

SELECT HOUR(datetime) as Hour, COUNT(*) as hits, SUM(clicks) 
FROM visitors_log_table 
WHERE datetime >= CURDATE() 
GROUP BY Hour 
WITH ROLLUP

It works fine. But I don't need GROUP BY for the clicks column. I only need the WITH ROLLUP result for it.

This query is just an example. In my query I have many more columns like the clicks column where I don't need a GROUP BY.

My table is very, very large, so performance is important. The datetime column is indexed. So should I do a seperate query, or should I leave it like that, or...?

  • I think that I see what you're driving at, but could you post (at SQLFiddle.com) DDL for your visitors_log_table and some DML with sample data, the result you desire and the logic you used to obtain that result. It would help clarify your request. – Vérace Jun 25 '14 at 22:40
  • Is performance of this query actually an issue? – Pieter Geerkens Sep 28 '14 at 1:42
1

Take a look at my answer here - you could take a similar query and use MySQL events (see here) to add rows to an aggregation table on an hourly basis - say at 5 minutes past the hour, perform a query for the previous hour's clicks and hits - that way you'd only be querying an hour at a time and I presume that the performance would be OK for a small subset of your results. Since it's logging you're doing, any logged data isn't going to change, so querying 1 hour at a time makes sense - i.e. (pseudocode)

SELECT HOUR<my_time_field> AS Hour, <my_aggregates> 
FROM <my_table> 
WHERE MyTime 
BETWEEN (Now - 65mins) AND (Now-5mins);
GROUP BY Hour

Something like that might be a solution to your issue?

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.