Hi Hank, all!
Hank wrote: > On Fri, Oct 1, 2010 at 2:34 PM, Joerg Bruehe <joerg.bru...@oracle.com> wrote: >> Hi! >> >> >> Hank wrote: >>> On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson >>> <b...@biz-comm.com> wrote: >>>> On 9/28/10 8:33 PM, Chris W wrote: >>>> >>>>> SELECT * >>>>> FROM announcements >>>>> WHERE announcements_expiredate > CURDATE() >>>>> AND announcements_postdate <= CURDATE() >>>>> ORDER BY announcements_expiredate ASC >>> Or how about something like this: >>> >>> SELECT * >>> FROM announcements >>> WHERE CURDATE() between announcements_postdate and announcements_expiredate >>> ORDER BY announcements_expiredate ASC >> The syntax is correct, but I don't think this statement will be >> optimized as well as the other proposal: >> BETWEEN is intended for "column BETWEEN const1 AND const2", >> whereas your statement is "const BETWEEN column1 AND column2". >> > > But that only really matters if there are indexes on the column1 and > column2 fields. Correct. But given that the typical question here is "how can I make it faster?" and the typical answer "create proper indexes", we should assume there are (or will be) indexes defined. > > And for the optimizer, wouldn't it make sense to map BETWEEN into two > comparison statements "(column>const1 and column<=const2)" or > "(const>column1 and const<=column2)" where both scenarios the > optimizer may be able to use indexes on the fields? It's exactly the > same as the other proposal: > > "CURDATE() > announcements_postdate and CURDATE()<= > announcements_expiredate" which still is using two different fields > for the comparisons... so wouldn't both scenarios end up in the exact > same place? As I wrote: Your syntax is correct. I should have added: And the semantics are correct, too. Your approach is perfectly valid. However, there several discussions about the optimizer at various lists and forums which mostly agree that it might handle some constructs even better. I don't have expert knowledge there, I can't tell what the optimizer will do with the two statements proposed, I just fear it might not handle "const BETWEEN column1 AND column2" as clever as you hope. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
