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
avoidHello,
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
usingMySQL has to do a filesorting on the data returned. (I assume you're
-+-------------+---------------------+------+-----+---------------------+---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] | |
http://lists.mysql.com/[EMAIL PROTECTED]------------+
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:
--
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.
