Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am designing a db that should include a table with fields for datetime scheduling, so as I know what kind of queries will be made​​ periodically I would like to design the table for them to be as simple as possible.

Every minute I will check table to see if "it's time!" but..

if I put fields like begin_time, end_time in every row, then when I do the query the database engine has to walk all rows, for example with a consult like

SELECT * FROM table WHERE begin_time<=actualTime AND end_time>=actualTime

Perhaps I am thinking as a closer-to-hardware programmer and overthinking but I would like to avoid some naive design that overload the server with a simple scheduler..

Other idea I have is to make a table with ranges alone and a key so if I make a query at the moment of insert perhaps there are repeated ranges and this will reduce the db work.

I've read this related question but doesn't adress same issue and have few answers Unique time range or database schema for a scheduling system so I hope to get some clues on this.

share|improve this question
1  
Will the (begin_time, end_time) intervals overlap or not? –  ypercube Dec 14 '12 at 14:00
    
@ypercube it depends, on the first approach the table will contain id's and date ranges, etc, for same id (because there are multiple ranges for every id) so ranges will not overlap, but of course they will overlap for different id's. In the second approach they will not overlap by design because the idea is to identify (on insert) if the range was already used (slow insert) and assign a unique id for that range to reuse in the first table. –  Hernán Eche Dec 14 '12 at 14:12

1 Answer 1

up vote 2 down vote accepted

...if I put fields like begin_time, end_time in every row, then when I do the query the database engine has to walk all rows, for example with a consult like

If you create an index on begin_time and an index on end_time, your dbms should use the indexes to avoid a full table scan. I just tried that with PostgreSQL and a table of 50k rows. It used the index on begin_time, and returned in 0.02ms. A multi-column index (an single index on both begin_time and end_time) might perform even better.

You might want to update a column in those rows to make sure the notification (or whatever) doesn't happen every minute between begin_time and end_time.

share|improve this answer
    
Indexes, you are right I'll let dbms do the work. Yes a column will be updated for that purpose, thanks –  Hernán Eche Dec 14 '12 at 14:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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