Design database to query a date range for scheduling

Question

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.

Answer 1

...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.