DATE range calucation optimization

Databases
1

I would like to find out if someone can help me to optimize the date range calculation in the following query:


SELECT
	USERID,
	birthday
FROM members
WHERE
	birthday BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 100 YEAR) AND DATE_SUB(CURRENT_DATE(), INTERVAL 0 YEAR)
	OR birthday='0000-00-00'
;

I want a speedy birthday comparison over huge users database.
Thank you.

2

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’

3

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 :wink: