How do I run the same query across X number of days efficiently?

Question

We have a MySQL database that we are using to measure automated test successes and failures. Now we need to query the database to find out how many failures happened per day and what the cause was. The queries I've come up with so far go something like this:

select * from failures 
where time >= '{time}' and time <= '{time} 23:59:59' and cause = '{fail_type} failed'

I have a couple of variations of this query. The problem I am running into is that we need to query the database for this information for 1, 7 and 56 days. This means I run the query 1 time, 7 times and 56 times, respectively. As the number goes up, the length of time for the query gets really long. I also have to run each of these queries for each failure type, which really adds to the query time.

Is there a more efficient way to query the database? So far, the closest question I've found on Stack was this one:

Select records by time in interval between 12:00:00 and 18:00:00 on every day

Note: I am running this query in a Flask-based web app and then graphing the results.

why not group by 'day'? to get raw aggregates from db rather than asking db 56 times? or am i missing something? — user3012759, Apr 20 '15 at 13:22
I haven't done SQL in depth for a few years. Can you give an example? — Mike Driscoll, Apr 20 '15 at 13:39
You mean you want to see the failures for last 1, 7 and 56 days? Do you have a date field in your table? — Krish, Apr 20 '15 at 13:50
@MikeDriscoll http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year hope this will help you — user3012759, Apr 20 '15 at 14:01

Krish · Accepted Answer · 2015-04-20T14:19:32.387

1

if you post a sample output it would help us to understand more. You can group by failure type and put a date interval in your where clause.

something like:

SELECT * from tbl_failures
WHERE (cause = '{fail_type} failed') AND (date_field between date_sub(curdate(), interval 7 day) and curdate()))
Group by tbl_failures.Type

Same query for 7 days chart:

SELECT * from tbl_failures
WHERE (cause = '{fail_type} failed') AND (date_field between date_sub(curdate(), interval 1 week) and curdate()))
Group by tbl_failures.Type

and so on..

EDIT have added date range. With date between you can look back into a specific range like one week, one month excluding everything else.

edited Apr 20 '15 at 14:19

answered Apr 20 '15 at 14:04

Krish

5,917
2
14
35

The sample output probably isn't helpful. But I just ran it and it returned 51L, so that means we had 51 failures – Mike Driscoll Apr 20 '15 at 14:10
HAve added the date between range try this query and improve as per your needs – Krish Apr 20 '15 at 14:22
One of my co-workers ended up writing some SQL that was quite similar to this answer. Thanks for the help! – Mike Driscoll Apr 21 '15 at 18:03

How do I run the same query across X number of days efficiently?

1 Answers1