Is the DATE field a timestamp column?
What's your schema? What's your primary key?
I ask this because the real question is, "is it safe to assume that
new entries are for the current day it is inserted?"
If the answer to that question is yes, you can use an id field (or an
existing one) to find the FIRST and LAST record of the day, and get
all the id's in between.
Of course, that's based on a generic idea I have of what you're trying
to do. If you find the first entry of the day you're looking for
(SELECT * FROM PRODUCT WHERE DATE LIKE '2005-10-24%' LIMIT 1;) and the
first entry for the next day (SELECT * FROM PRODUCT WHERE DATE LIKE
'2005-10-25%' LIMIT 1;) and find all the entries in between (this is
helpful if you use an ID index).
Also, I'm guessing here that the "help" you need is that your query
takes too long. . .you do not actually state why you need help, so I'm
just putting all my assumptions out there.
-Sheeri
On 10/24/05, Kevin Burton <[EMAIL PROTECTED]> wrote:
>
> OK.
>
> I need help with the following query:
>
> SELECT * FROM PRODUCT WHERE DATE > ? ORDER BY PRICE;
>
> Basically find products created since a given date and order by prices.
>
> I could put an index of DATE, PRICE but it will have to resort to a
> filesort since DATE isn't a constant value.
>
> I was thinking of using a DAY column so that I can just find values
> in the last day.
>
> Then I could rewrite it as:
>
> SELECT * FROM PRODUCT WHERE DAY = ? ORDER BY PRICE;
>
> and place an index on DAY, PRICE at which point I'd be able to order
> by the index.
>
> Which would work really well.
>
> The problem is that at midnight there would be no results since DAY
> isn't really a floating window.
>
> I could use DAY IN { ? ? } and then ORDER BY PRICE but it would have
> to use a filesort again.
>
> Is there any interesting way people have solved this problem in the
> past?
>
> Kevin
>
>
> Kevin A. Burton, Location - San Francisco, CA
> AIM/YIM - sfburtonator, Web - http://www.feedblog.org/
> GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
