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.
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
