Albert, it seems like the first query could be simplified, like so:
SELECT id, subject, updated FROM mrldisc WHERE updated > SUBDATE(NOW(), INTERVAL 48 HOUR) ORDER BY updated DESC LIMIT 50 This might help it hit the index you've created on the UPDATED column. I know there have been bugs here and there with the optimizer and IN subqueries not hitting indices, especially in earlier 4.1.x releases. If you have a large number of rows with recent values for the UPDATED column, MySQL may be doing a table scan. This is an instance where test/development scenarios don't always work quite as well as real data. However, 6-12 seconds for a 5000 row table does seem slow ... Can you post the output of EXPLAIN <query> ? That will help us see how MySQL is planning to run your query, and may also reveal a little bit about your data. Thanks, Dan On 10/31/06, Albert Padley <[EMAIL PROTECTED]> wrote:
I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT thread_id FROM mrldisc WHERE updated > SUBDATE(NOW(), INTERVAL 48 HOUR))) ORDER BY updated DESC LIMIT 50 Here is the table schema: CREATE TABLE `mrldisc` ( `id` int(14) NOT NULL auto_increment, `thread_id` int(14) default NULL, `author` varchar(100) NOT NULL default '', `state` varchar(25) NOT NULL default '', `subject` varchar(100) NOT NULL default '', `message` longtext NOT NULL, `date` varchar(50) NOT NULL default '', `jdate` varchar(50) default '0000-00-00 00:00:00', `statespecific` varchar(25) NOT NULL default '', `mainthread` char(1) NOT NULL default '', `email` varchar(100) NOT NULL default '', `threadcount` int(11) NOT NULL default '0', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP, `ip_address` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`), KEY `statespecific` (`statespecific`), KEY `state` (`state`), KEY `updated` (`updated`), KEY `email` (`email`), KEY `mainthread` (`mainthread`), KEY `jdate` (`jdate`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `message` (`message`), FULLTEXT KEY `subject` (`subject`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have tried other queries like: SELECT DISTINCT * FROM mrldisc WHERE updated > SUBDATE(NOW(), INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50 This one was close, but returned the oldest row in the group rather than the most recent row. I've also thought about doing the search with a temporary table, but haven't gotten very far with that. Any pointers would be greatly appreciated. Thanks. Al Padley
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
