Good idea. MySQL appears to use the index and skip between intervals. (I'm not sure
it always did...)
On the Feb. 29, I'd be disinclined to rely on MySQL's (or any other DB's) handling of
invalid dates. Note that it appears to take '2002-02-29' to mean Mar. 1, 2002, and
'2002-02-30' to mean Mar 2, 2002. So your query below, for 2-29, would give the
counts for Mar. 1 in the prior years. The original query you asked about gave zero
for 2-29. Figure out what you want for a result--and don't forget that in 2005, there
isn't a 2-29, so you won't ever see the values for 2004-02-29. Maybe you only care
about an approximate result, and none of this matters.
----- Original Message -----
From: Dirk Schippers
To: Bill Easton
Cc: [EMAIL PROTECTED]
Sent: Monday, September 20, 2004 6:06 PM
Subject: Re: Query takes terribly long
Hello Bill,
Your explanation gave me an idea:
SELECT id
FROM story
WHERE ((putdatetime>='2003-09-20' AND putdatetime<'2003-09-21' )
OR (putdatetime>='2002-09-20' AND putdatetime<'2002-09-21')
OR (putdatetime>='2001-09-20' AND putdatetime<'2001-09-21'))
AND put=1 AND front=1
ORDER BY putdatetime DESC
LIMIT 0,30;
I know there are no stories before 2001, so I know where to stop with building the
query. As years will pass, I don't think the query will become much slower with the
extra OR lines added as they are all checked in the index.
The only thing I'm worried about with this is, what about februari 29th, but Mysql
seems to handle (putdatetime>='2002-02-29' AND putdatetime<'2002-02-30') (yes even
that) quite well), but if it turns out to be a problem, I'll gladly code an exception
for that day.
Thanks for your tips, the query now always takes 0.03s in stead of about 0.33s.
Dirk.
Bill Easton wrote:
Dirk,
If you use a function on the column, MySQL will not use the index on that
where clause. You need to somehow arrange to not use a function
on the column with the index.
Here's the query you asked about.
SELECT id FROM story
WHERE MONTH(putdatetime) = MONTH('2004-09-19')
AND DAYOFMONTH(putdatetime) = DAYOFMONTH('2004-09-19')
AND YEAR(putdatetime) < YEAR('2004-09-19')
AND put=1 AND front=1
ORDER BY putdatetime DESC
LIMIT 0,30
I'm presuming you have an index on (put, front, putdatetime). MySQL will
read
all of the rows with put=1 and front=1, since it can't make use of the
YEAR(...) < YEAR(...) condition while it is running the query optimizer.
It looks like you are asking for the 30 latest rows for a given month and
day, but
for any year before the current one. Try replacing the YEAR(...) test with:
putdatetime < '2003-09-20'
MySQL should use the index to select only 30 records. If there were 30
records
last year, it will be fast. If it has to go to the year before, it will
still read all of the
earlier records, until it has found 30, but it will skip over the latest
year's records.
You can use
putdatetime < ('2004-09-19' + interval 1 DAY - interval 1 YEAR)
and let MySQL do the calculation. Note that you still get the rignt answer
for '2004-02-29'.
Of course, if you really want just the data for the same day last year, you
could use
SELECT id FROM story
WHERE putdatetime >= '2003-09-19' and putdatetime < '2003-09-20'
AND put=1 AND front=1
AND (MONTH(putdatetime) <> 2 OR DAY(putdatetime) <> 29)
ORDER BY putdatetime DESC
LIMIT 0,30
HTH, Bill
========== Dirk Schippers wrote: ==========
Date: Sun, 19 Sep 2004 18:35:24 +0200
From: Dirk Schippers <[EMAIL PROTECTED]>
To: Jocelyn Fournier <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED]
Subject: Re: Query takes terribly long
No,
I'm quiet sure that isn't the case as I am testing the optimized version
of my website on another database.
Hm, I'll let you know if I find out what's causing it.
If you have any more ideas, please inform me about them!
Storing day, month and year in other columns seems a little overkilling
for what it is used for, so I think my visitors should accept that that
query is a slow one.
Thanks for all your help!
Dirk.
Jocelyn Fournier wrote:
Hi,
The query is still slow because MySQL can't use the index to search the
right putdatetime.
So unless you store in seperated columns day, month and year, so that you
can query on those fields without a function, this will remain slow.
Are you sure no other "long" queries are running on this table, which could
lock the table (and thus explain why sometimes it takes up to 22 seconds) ?
Regards,
Jocelyn Fournier
www.presence-pc.com
----- Original Message -----
From: "Dirk Schippers" <[EMAIL PROTECTED]>
To: "Jocelyn Fournier" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, September 19, 2004 5:49 PM
Subject: Re: Query takes terribly long
Hello,
Indeed, when I use {put,front,putdatetime}, no filesort is used. But the
query is still very slow.
It takes about 2 seconds. And again, mysql prefers not to use your key,
but I can force that of course.
I did an ANALYZE TABLE but that didn't change things, it might have sped
up the other queries on the table.
Probably this query can't be sped up because of the functions I use.
Is there another thing I can try to speed the query up?
And another question, is it normal that even when not using the cache
(always executing RESET QUERY CACHE; before the query), the query
sometimes only takes 0.33 seconds, and at other times the query takes 22
seconds? Is this something everyone here experiences? Or is there
something wrong with my configuration?
Regards,
Dirk.
Jocelyn Fournier wrote:
Hi,
I would say for this one you need an index on (put, front, putdatetime)
to
get rid of the filesorting.
Note that because you're transforming the putdatetime column with
functions,
MySQL will not be able to use index infos of putdatetime.
So it will use put and front index information to find the result, and
putdatetime to order the result.
If you add topcategory in your query, you will see MySQL will use the
index
(put, front, topcategory, putdatetime) without filesorting.
Regards,
Jocelyn Fournier
www.presence-pc.com
----- Original Message -----
From: "Dirk Schippers" <[EMAIL PROTECTED]>
To: "Jocelyn Fournier" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, September 19, 2004 11:55 AM
Subject: Re: Query takes terribly long
--
Schippers Dirk
Zaakvoerder Frixx-iT
http://www.frixx-it.com
------------------------------------------------------------------------
Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen.
