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