SQL: Optimize insensive SELECTs on DateTime fields
- by Fedyashev Nikita
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.
