MySQL Forums
Forum List  »  Optimizer & Parser

Re: Desperate: BETWEEN and ORDER BY on different columns
Posted by: Rick James
Date: September 05, 2011 12:03AM

SELECT  id
    FROM  members
    WHERE  (sex = 2)
      AND  (birthdate BETWEEN '1980-09-01' AND  '1984-09-01')
    ORDER BY  rnd
    LIMIT  10;
Did you mean to include '1984-09-01'? (A common bug in date ranges.) Suggest:
AND birthdate >= '1980-09-01'
AND birthdate < '1980-09-01' + INTERVAL 4 YEAR

You could create an index that has all the relevant fields; this might speed up the query:
INDEX(sex, birthdate, rnd, id)
or
INDEX(sex, rnd, birthdate, id)


If you are really fetching bulky fields, not just id, then this is likely to help:

SELECT  m.a, m.b, ...
    FROM  members m
    JOIN  
      ( SELECT  id
            FROM  members
            WHERE  (sex = 2)
              AND  birthdate >= '1980-09-01'
              AND  birthdate < '1980-09-01' + INTERVAL 4 YEAR
            ORDER BY  rnd
            LIMIT  10 ) x ON x.id = m.id
(Again, you need the INDEX suggested above.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Desperate: BETWEEN and ORDER BY on different columns
1106
September 05, 2011 12:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.