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 where it makes sence.