Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

fast query for daily activity?

I have a large table of daily activity, with a timestamp to the second as one of the columns.
There are several hundred million rows in the yearly log. The timestamp is indexed.
I need to produce a daily summary table for the year. For example, the report would have daily averages for the yearly log. Using count() group by day - is very slow. Is there a way to optimize this query?
0
pillmill
Asked:
pillmill
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, there are possibilities.

you could do a daily accumulation into a dedicated table, and update it once per day, taking only the records from the previous day ...

say you create the table like this:
create table activity_daily_counts ( date_group date primary key, count_value int ) 

Open in new window


and you create a job that does the daily update, using this procedure:
create procedure update_daily_activity_count( @date date = null )
as
begin
  if @date is null   set @date = dateadd(day, -1, cast(getdate() as date))
  
  delete activity_daily_counts where date_group = @date
  insert into activity_daily_counts ( date_group, count_value )
   select @date, count(*)
     from  your_table
   where your_date_field  >= @date
     and your_date_field < dateadd(day, 1, @date)

end 

Open in new window


and you populate that table by either running the procedure once for every date value you can have, or simply by the normal one-shot insert:
  insert into activity_daily_counts ( date_group, count_value )
   select cast(your_date_field as date), count(*)
     from  your_table
group by cast(your_date_field as date)

Open in new window


and your query on the daily_counts table will be VERY fast-
0
 
kmslogicCommented:
This sounds like an analytics problem, so the big solution might be to look at one of the OLAP servers available for MySQL

I think what'd I try myself to solve this would be to run a nightly process to produce daily totals up through (today - 2 days), then combine those totals with a query of just the last two days from the detail records to generate your daily summary table for the year.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, I put MS SQL syntax, you have MySQL ...
but the principle is the same ...
0
 
kmslogicCommented:
Yes sorry I was typing my answer and overlapped angel.  He's the real expert, listen to him!
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now