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