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
> >
> >
> >
> >
> >>Hello,
> >>
> >>Now this I don't understand, now the query takes only 0.05s on any of
> >>the two indexes.
> >>I didn't do anything! Not even the analyze table I was going to try!
> >>
> >>I'll keep you posted, thank you for the explanation, I understand the
> >>reason for the filesort now.
> >>
> >>Dirk.
> >>
> >>Jocelyn Fournier wrote:
> >>
> >>
> >>
> >>>Hi,
> >>>
> >>>Well that's strange MySQL says more rows will be returned.
> >>>How many times does the query takes ?
> >>>And if you run ANALYZE TABLE on your table, does this change anything
> >>>
> >>>
> >about
> >
> >
> >>>rows statistics ?
> >>>
> >>>MySQL do not need filesort with this kind of index, because it can use
> >>>
> >>>
> >the
> >
> >
> >>>index to retrieve the row in the right order directly.
> >>>
> >>>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:37 PM
> >>>Subject: Re: Query takes terribly long
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>>Hello,
> >>>>
> >>>>I added the index you said, but mysql (yes 4.x) still prefers using
the
> >>>>other index {put,front,topcategory,approvedby}.
> >>>>And indeed, he uses the filesort. When I force into using your index,
it
> >>>>seems to be even slower (more rows but no filesort as you said)
> >>>>
> >>>>But I have a question about that, why would your index avoid a
filesort?
> >>>>I don't understand that.
> >>>>
> >>>>Any other suggestion on how to speed up?
> >>>>
> >>>>Anyway, thanks for all the help,
> >>>>Dirk.
> >>>>
> >>>>Jocelyn Fournier wrote:
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>Hi,
> >>>>>
> >>>>>You can try to add an index on (put,front,topcategory,putdatetime) to
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>avoid
> >>>
> >>>
> >>>
> >>>
> >>>>>MySQL has to do a filesorting on the data returned. (I assume you're
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>using
> >>>
> >>>
> >>>
> >>>
> >>>>>MySQL 4.x)
> >>>>>
> >>>>>Regards,
> >>>>>Jocelyn Fournier
> >>>>>www.presence-pc.com
> >>>>>
> >>>>>----- Original Message -----
> >>>>>From: "Dirk Schippers" <[EMAIL PROTECTED]>
> >>>>>To: <[EMAIL PROTECTED]>
> >>>>>Sent: Saturday, September 18, 2004 10:25 PM
> >>>>>Subject: Query takes terribly long
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>Hello,
> >>>>>>
> >>>>>>I have this MyISAM table "story" (52MB):
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
>
>>>+-------------+---------------------+------+-----+---------------------+-
-
> >>>
> >>>
> >-
> >
> >
> >>>>
> >>>>
> >>>-
> >>>
> >>>
> >>>
> >>>
> >>>>>------------+
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>| Field | Type | Null | Key | Default
> >>>>>>
> >>>>>>
> >|
> >
> >
> >>>>>>Extra |
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
>
>>>+-------------+---------------------+------+-----+---------------------+-
-
> >>>
> >>>
> >-
> >
> >
> >>>>
> >>>>
> >>>-
> >>>
> >>>
> >>>
> >>>
> >>>>>------------+
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>| id | int(10) unsigned | | PRI | [NULL]
> >>>>>>
> >>>>>>
> >|
> >
> >
> >>>>>>auto_increment |
> >>>>>>| sequelof | int(10) unsigned | | | 0
> >>>>>>| |
> >>>>>>| prevsequel | int(10) unsigned | | | 0
> >>>>>>| |
> >>>>>>| userid | int(10) unsigned | | MUL | 0
> >>>>>>| |
> >>>>>>| title | varchar(255) | YES | | [NULL]
> >>>>>>| |
> >>>>>>| topcategory | tinyint(3) unsigned | | | 1
> >>>>>>| |
> >>>>>>| category | tinyint(3) unsigned | | | 1
> >>>>>>| |
> >>>>>>| rated | tinyint(1) unsigned | | | 0
> >>>>>>| |
> >>>>>>| language | tinyint(3) unsigned | | | 0
> >>>>>>| |
> >>>>>>| font | int(10) unsigned | | | 1
> >>>>>>| |
> >>>>>>| fontsize | varchar(4) | | | 2
> >>>>>>| |
> >>>>>>| story | mediumtext | | |
> >>>>>>| |
> >>>>>>| note | text | | |
> >>>>>>| |
> >>>>>>| adddatetime | datetime | | | 0000-00-00
00:00:00
> >>>>>>| |
> >>>>>>| putdatetime | datetime | | MUL | 0000-00-00
00:00:00
> >>>>>>| |
> >>>>>>| put | tinyint(1) | | MUL | 0
> >>>>>>| |
> >>>>>>| putby | int(10) unsigned | | | 0
> >>>>>>| |
> >>>>>>| approvedby | int(10) unsigned | | | 0
> >>>>>>| |
> >>>>>>| blockbot | tinyint(1) unsigned | | | 0
> >>>>>>| |
> >>>>>>| front | tinyint(1) | | | 1
> >>>>>>| |
> >>>>>>| selection | tinyint(1) | | | 0
> >>>>>>| |
> >>>>>>| timesread | int(10) unsigned | | | 0
> >>>>>>| |
> >>>>>>| ipnumber | varchar(20) | | |
> >>>>>>| |
> >>>>>>| words | int(11) | | | 0
> >>>>>>| |
> >>>>>>| review | tinyint(1) unsigned | | MUL | 0
> >>>>>>| |
> >>>>>>| avgscore | int(10) unsigned | YES | | [NULL]
> >>>>>>| |
> >>>>>>| numvotes | int(10) unsigned | YES | | [NULL]
> >>>>>>| |
> >>>>>>| numreacts | int(10) unsigned | YES | | [NULL]
> >>>>>>| |
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
>
>>>+-------------+---------------------+------+-----+---------------------+-
-
> >>>
> >>>
> >-
> >
> >
> >>>>
> >>>>
> >>>-
> >>>
> >>>
> >>>
> >>>
> >>>>>------------+
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>with indexes: id = primary, putdatetime, userid,
> >>>>>>{put,front,topcategory,approvedby} and review.
> >>>>>>
> >>>>>>If I want to know the 30 most recently added and approved items, I
do
> >>>>>>the following simple query:
> >>>>>>SELECT id FROM story WHERE put=1 AND front=1 AND topcategory=1 ORDER
> >>>>>>
> >>>>>>
> >BY
> >
> >
> >>>>>>putdatetime DESC LIMIT 0,30
> >>>>>>
> >>>>>>This query sometimes takes up to 10 seconds!!!
> >>>>>>I must admit that the cache is disabled at this moment (I want to
see
> >>>>>>realistic timings), but still I think 10 seconds is terribly long to
> >>>>>>retrieve 30 id's!
> >>>>>>
> >>>>>>Explain tells me that it is using the index
> >>>>>>{put,front,topcategory,approvedby} and narrows the query to 5475
rows
> >>>>>>(of the total of 18818).
> >>>>>>
> >>>>>>The table will certainly grow a lot in the future so I am very
worried
> >>>>>>about the performance.
> >>>>>>What can I do about this? Is there any way to improve this?
> >>>>>>Enabling the cache is not an option as the data in the table is
> >>>>>>
> >>>>>>
> >altered
> >
> >
> >>>>>>a lot.
> >>>>>>
> >>>>>>Anyone?
> >>>>>>
> >>>>>>Dirk.
> >>>>>>
> >>>>>>--
> >>>>>>
> >>>>>>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]
> >>>
> >>>
> >>>
> >>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>--
> >>>>
> >>>>Schippers Dirk
> >>>>Zaakvoerder Frixx-iT
> >>>>http://www.frixx-it.com
>
>>>>------------------------------------------------------------------------
> >>>>Bezoek ook http://www.verhalensite.com voor uw literaire
tekortkomingen.
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>
> >>--
> >>
> >>Schippers Dirk
> >>Zaakvoerder Frixx-iT
> >>http://www.frixx-it.com
> >>------------------------------------------------------------------------
> >>Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen.
> >>
> >>
> >>
> >>
> >
> >
> >
> >
>
> --
>
> 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]
