Re: Query takes terribly long

[Dirk Schippers]

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.