SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict bimalpoudel's Avatar
    Join Date
    Feb 2009
    Location
    Kathmandu, Nepal
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow DATE range calucation optimization

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

    Code:
    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.
    Bimal Poudel @ Sanjaal Framework over Smarty Template Engine
    ASKING INTERESTING QUESTIONS ON SITEPOINT FOURM

    Hire for coding support - PHP/MySQL

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,095
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    See here http://stackoverflow.com/questions/4...rformance-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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by bimalpoudel View Post
    I want a speedy birthday comparison over huge users database.
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •