If you want to speed it up, you have to make it use an index. You need to add a WHERE or an ORDER BY clause. Have you tried : SELECT Message_ID, Body FROM Body_etc ORDER BY Message_ID LIMIT N,M
Of course, I assume that Message_ID is indexed ;)
Regards, Joseph Bueno
Nick Arnett wrote:
-----Original Message----- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2003 9:17 PM
Please post the query and the output of running it thru EXPLAIN.
It is likely sorting the results without an index and having to weed thru more and more records the farther back you look in the list.
I'm fairly certain that's true. The query is very simple: "SELECT Message_ID, Body FROM Body_etc LIMIT N,M". Those are the only fields in the table; Message_ID is a CHAR field, Body is TEXT.
Here's what EXPLAIN gives:
+----------+------+---------------+------+---------+------+---------+------- + | table | type | possible_keys | key | key_len | ref | rows | Extra | +----------+------+---------------+------+---------+------+---------+------- + | body_etc | ALL | NULL | NULL | NULL | NULL | 1586994 | | +----------+------+---------------+------+---------+------+---------+------- +
Sorry if I didn't make that clear in my first posting. I can't quite see how to speed things up, though it dawned on me that perhaps I could add a sequence field, index it, then use it, rather than the LIMIT clause, to select the records. It was quite fast at the beginning and caught me by surprise that it slowed down so much.
I'm getting there, slowly, by bumping up the max_packet_size and doing three INSERTs of 5,000 for every SELECT, so I'm grabbing about 15,000 records at a time, greatly reducing the number of SELECTs from when I was only getting 1,000. Still taking hours, but I'll be letting it run all night.
By the way, I noticed that in one of your presentations, you noted that Handler_read_rnd_next is better if it's smaller. It's at 5.7 million now, after doing about 900K records. I'm assuming that's an indication that there's some inefficiency, but I'm still stumped as to how to address it. The machine I'm running MySQL on has 1 GB of memory, so I have lots of headroom to play with.
And since people tend to start asking questions when they realize I'm indexing large amounts of e-mail, etc., I'll explain a little. I'm doing traffic and content analysis to track what's going on in certain large software development communities.
Thanks!
Nick
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
