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]
