I would like to find out if someone can help me to optimize the date range calculation in the following query:
birthday BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 100 YEAR) AND DATE_SUB(CURRENT_DATE(), INTERVAL 0 YEAR)
I want a speedy birthday comparison over huge users database.
See here http://stackoverflow.com/questions/4382892/whats-mysqls-between-performance-over. I don’t see anything that will help other than POSSIBLY allowing nulls in birthday and switching the default value to NULL so that you are searching for birthday is null rather than birthday = ‘0000-00-00’
make sure there’s an index on birthday
i can think of a few ways to make your query easier to read, but that’s not what you asked