MySQL Forums
Forum List  »  Partitioning

Re: What Method is Best
Posted by: Rick James
Date: September 14, 2012 11:11PM

First let me describe an algorithm involving TV show times.

Most shows last 30-60 minutes. Some last 2-3 hours. A few last all day, or even longer (shopping channel, Olympics, etc)

You goal (for my TV example) is to find all the shows in an arbitrary 4-hour window. (This is not quite your example, but more on that later.)

Build, and maintain, an _extra_ table that has two columns:
* A datetime, rounded down to an hour; let's call it `hour_id`.
* `show_id` for joining to the main table.
A row says that `show_id` occurs during part or all of the hour starting at `hour_id`. Note that long shows will have multiple rows in this table.

Use InnoDB and have
PRIMARY KEY(hour_id, show_id) -- for the main query (below)
INDEX(show_id) -- for maintenance

To find all the shows in the 4-hour window, you need to scan 4 (or 5, if not on hour boundary) values of the datetime:
SELECT DISTINCT show_id FROM extra WHERE `hour_id` BETWEEN ...;
Then look up the show info (by show_id) in the main table, and further verify that they really fit in the desired window.

Back to your question. You say
WHERE startTime BETWEEN 'A' AND 'B' OR endTime BETWEEN 'B' AND 'C'
(This seems rather strange.)
After you have built the "extra" table I described, you would search for anything living BETWEEN 'A' and 'C'. Then double check.

With this technique, there is no need for, or performance advantage in using, PARTITIONing. There could be an advantage in PARTITIONing if you need to purge old data.

Options: ReplyQuote

Written By
September 12, 2012 03:12PM
September 14, 2012 02:36AM
September 14, 2012 05:30AM
Re: What Method is Best
September 14, 2012 11:11PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.