Dan,
Actually you were on the right track. I changed your suggested query
to the following and it seems to work and is a lot quicker.
SELECT id, subject, updated FROM mrldisc WHERE updated > SUBDATE(NOW
(), INTERVAL 48 HOUR) AND mainthread = 'T' ORDER BY updated DESC
LIMIT 50
Thanks.
Al
On Oct 31, 2006, at 4:01 PM, Albert Padley wrote:
Dan,
On Oct 31, 2006, at 3:41 PM, Dan Buettner wrote:
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 query won't work. The table contains threaded messages. The
thread_id refers back to the id of the first message in the thread.
The above query would return a list of all messages in the last 48
hours when all I want is to return the original message in the thread.
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 ...
In our test database it's probably less than 50 rows that have been
updated in the last 48 hours.
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.
1 PRIMARY mrldisc index updated 4 4888 Using
where
2 DEPENDENT SUBQUERY mrldisc index_subquery thread_id,updated
thread_id 5 func 8 Using index; Using where
Thanks.
Al
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/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
