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
> >
> >
> >
> >
> >>Hello,
> >>
> >>Sorry to bother you again, but now I have the following, bit more
> >>complicated query on the same table:
> >>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
> >>
> >>The date is not Now() because the cache won't optimize the query then
> >>and now the cache can use the same query all day.
> >>But EXPLAIN says that the same index (yours) is used but he still uses a
> >>filesort, on almost all the rows of the table (18775). You can imagine
> >>how slow this query is.
> >>
> >>Is there a way to improve this one?
> >>
> >>Thanks and sorry about the lot of messages you are receiving, but I'm
> >>learning a lot here!
> >>
> >>Dirk.
> >>
> >>Jocelyn Fournier wrote:
> >>
> >>
> >>
> >>>Are you sure this not because your query is cached by the MySQL query
> >>>
> >>>
> >cache
> >
> >
> >>>now ? (try RESET QUERY CACHE before testing your query to be sure)
> >>>
> >>>Regards,
> >>> Jocelyn Fournier
> >>> www.presence-pc.com
> >>>----- Original Message -----
> >>>From: "Dirk Schippers" <[EMAIL PROTECTED]>
> >>>To: "Jocelyn Fournier" <[EMAIL PROTECTED]>
> >>>Cc: <[EMAIL PROTECTED]>
> >>>Sent: Saturday, September 18, 2004 11:53 PM
> >>>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.
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
