MySQL: Optimizing query for records within date range

Question

I have a table (logs) that has the following columns (there are others, but these are the important ones):

id (PK, int)
Timestamp (datetime) (index)
Duration (int)

Basically this is a record for an event that starts at a time and ends at a time. This table currently has a few hundred thousand rows in it. I expect it to grow to millions. For the purpose of speeding up queries, I have added another column and precomputed values:

EndTime (datetime) (index)

To calculate EndTime I have added the number of seconds in Duration to the Timestamp field.

Now what I want to do is run a query where the result counts the number of rows where the start (Timestamp) and end times (EndTime) fall outside of a certain point in time. I then want to run this query for every second for a large timespan (such as a year). I would also like to count the number of rows that start on a particular point in time, and end at a particular point in time.

I have created the following query:

SELECT 
    `dates`.`date`, 
    COUNT(*) AS `total`, 
    SUM(IF(`dates`.`date`=`logs`.`Timestamp`, 1, 0)) AS `new`,
    SUM(IF(`dates`.`date`=`logs`.`EndTime`, 1, 0)) AS `dropped` 
FROM 
    `logs`,
    (SELECT 
        DATE_ADD("2010-04-13 09:45:00", INTERVAL `number` SECOND) AS `date` 
        FROM numbers LIMIT 120) AS dates
WHERE dates.`date` BETWEEN `logs`.`Timestamp` AND `logs`.`EndTime` 
GROUP BY `dates`.`date`;

Note that the numbers table is strictly for easily enumerating a date range. It is a table with one column, number, and contains the values 1, 2, 3, 4, 5, etc...

This gives me exactly what I am looking for... a table with 4 columns:

date
total (the total rows that start and end outside the current point in time)
new (rows that start at this point in time)
dropped (rows that end at this point in time)

The trouble is, this query can take a significant amount of time to execute. To go through 120 seconds (as shown in the query), it takes about 10 seconds. I suspect that this is about as fast as I am going to get it, but I thought I would ask here if anyone had any ideas for improving the performance of this query.

Any suggestions would be most helpful. Thank you for your time.

Edit: I have indexes on Timestamp and EndTime.

The output of EXPLAIN on my query:

"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"PRIMARY";"<derived2>";"ALL";NULL;NULL;NULL;NULL;"120";"Using temporary; Using filesort"
"1";"PRIMARY";"logs";"ALL";"Timestamp,EndTime";NULL;NULL;NULL;"296159";"Range checked for each record (index map: 0x6)"
"2";"DERIVED";"numbers";"index";NULL;"PRIMARY";"4";NULL;"35546940";"Using index"

When I run analyze on my logs table, it says status OK.

What have you indexed? What's the output of EXPLAINing your query? Have you analyzed the logs table? — outis
– outis, Commented Mar 1, 2011 at 22:44
@outis, thank you for your questions. I have added the information to the bottom of my post. — Brad
– Brad, Commented Mar 1, 2011 at 23:40
the analyze was more for its impact on how the query optimizer makes use of indices. Now I'm curious what the extended query plan is for your query, in particular to see if & how the query was rewritten. Would you post the result of SHOW WARNINGS after an EXPLAIN EXTENDED query. — outis
– outis, Commented Mar 2, 2011 at 0:21

DeltaLima · Accepted Answer · 2013-12-10 14:05:31Z

5

Note in the EXPLAIN output that the join type for the logs table is "ALL" and the key is NULL, which means a full table scan is scheduled. The "Range checked for each record" message means that MySQL uses the range access method on logs after examining column values from somewhere else in the result. I take this to mean that once dates has been created, MySQL can perform a ranged join on logs using the second and third indices (likely those on Timestamp and EndTime) rather than performing a full table scan. If you only have indices on Timestamp and EndTime separately, try adding an index on both, which might result in a more efficient join type (e.g. index_merge rather than range):

CREATE INDEX `start_end` ON `logs` (`Timestamp`, `EndTime`);

I believe (though could easily be wrong) that other items in the query plan either aren't really a concern or can't be eliminated. The filesort, as an example of the latter, is likely due to the GROUP BY. In other words, this is likely the extent of what you can do with this particular query, though radically different queries or approaches that address table storage format are still possibly more efficient.

edited Dec 10, 2013 at 14:05

DeltaLima

5,9441 gold badge18 silver badges33 bronze badges

answered Mar 2, 2011 at 0:18

outis

77.7k23 gold badges155 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brad Over a year ago

Thanks @outis, this is most helpful. I am not too familiar with optimizing queries, and this has given me a lot to go on. I will be doing more research into your suggestions. In the mean time, I thought of a more efficient way to do this, by limiting logs to just relevant entries, and taking the whole process 1 day at a time. I will run these queries separately on the application later for each day, and insert the data into a table for later use. I will post what I ended up with shortly, but I can't get to it right now. I think my ISP shut me off for loading down their server, heh.

outis Over a year ago

@Brad: if you haven't yet, make sure you read the MySQL documentation on query execution plans. My answer is based on information from that section.

Rasika · Accepted Answer · 2011-03-01 22:45:47Z

2

You could look at merge tables to speedup the processing. With merge tables, since the tables are split up, the indexes are smaller resulting in faster fetching. Also, if you have multiple processors, the searches can happen in parallel increasing the performance.

answered Mar 1, 2011 at 22:45

Rasika

1,99814 silver badges20 bronze badges

Collectives™ on Stack Overflow

MySQL: Optimizing query for records within date range

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related