SQL: Optimize insensive SELECTs on DateTime fields

Question

I have an application for scheduling certain events. And all these events must be reviewed after each scheduled time.

So basically we have 3 tables:

• items(id, name)
• scheduled_items(id, item_id, execute_at - datetime) - item_id column has an index option.
• reviewed_items(id, item_id, created_at - datetime) - item_id column has an index option.

So core function of the application is "give me any items(which are not yet reviewed) for the actual moment".

How can I optimize this solution for speed(because it is very core business feature and not micro optimization)?

I suppose that adding index to the datetime fields doesn't make any sense because the cardinality or uniqueness on that fields are very high and index won't give any(?) speed-up. Is it correct?

What would you recommend? Should I try no-SQL?

--

mysql -V
5.075

I use caching(memcached) where it makes sence.

updated.

Answer 1

I suppose that you actually want the items that are scheduled, but not reviewed after that scheduling?

Shouldn't the reviews be connected to the scheduled items instead of difrectly to the items? Now you have to compare the dates to see which reviews comes after one scheduled item but before the next. Also, if an item is scheduled twice with a short time between, you may end up with both reviews belonging to the second scheduling.

With this change you could easily pick out the unreviewed schedulings:

select i.id, i.name, s.execute_at
from items i
inner join scheduled_items s on s.item_id = i.id
left join reviewed_items r on r.scheduled_items_id = s.id
where r.id is null

As to your question:

I suppose that adding index to the datetime fields doesn't make any sense because the cardinality or uniqueness on that fields are very high and index won't give any(?) speed-up. Is it correct?

No, that is not correct. An index can be useful if the cardinality is high. An index is created by default for the unique id of a table, which of course has the highest cardinality possible.