Hi,
The only reason which could explain this is that statistics about the second
query are perhaps not uptodate.
Did you try to do an ANALYZE TABLE on it ?
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:05 AM
Subject: Re: Query takes terribly long
> Hello,
>
> Thanks for the hint!
> With RESET QUERY CACHE, I'm always sure that the cache is empty and with
> that, I've discovered that using the key as you said, with the datetime
> field included, is much!!! faster than the other key (no filesort is
used).
>
> But, the strange thing is that with the index
> {put,front,topcategory,approvedby} (where it takes up to 5 seconds to
> execute the query), explain tells me he has to process 5475 rows but
> with your index {put,front,topcategory,putdatetime} (which seems to
> always take up about 0.05 seconds, thank you very much), explain tells
> me he has to process 6243 rows. And that's probably the reason why MySQL
> keeps using my index and I need USE INDEX (...) to force him into using
> yours.
>
> But I don't understand the difference in rows to process... do you?
>
> For now, I will put USE INDEX (...) into the query as I also need the
> other key.
>
> 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]
