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
