Hello everybody,

I have two rather large table with TEXT fields where I need to do a
JOIN. All fields that are used in the JOIN and should be part of the
result are covered in an index, but the JOIN still uses temporary
tables and takes three hours (!) to complete.

This is my schema:

-- Table "spam_archive" DDL

CREATE TABLE `spam_archive` (
`id` int(10) unsigned NOT NULL auto_increment,
`source` varchar(50) default NULL,
`message` longtext NOT NULL,
`md5` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `HASH` (`md5`),
KEY `SOURCE` (`source`)
) ENGINE=MyISAM AUTO_INCREMENT=1960857 DEFAULT CHARSET=latin1;

-- Table "spam_plaintext" DDL

CREATE TABLE `spam_plaintext` (
`id` int(10) unsigned NOT NULL auto_increment,
`msghash` varchar(32) character set latin1 NOT NULL,
`plaintext` mediumtext character set latin1 NOT NULL,
`stockspam` tinyint(1) default NULL,
`images` tinyint(1) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `MSGHASH` USING BTREE (`msghash`,`stockspam`,`images`)
) ENGINE=MyISAM AUTO_INCREMENT=1768865 DEFAULT CHARSET=latin1
COLLATE=latin1_bin ROW_FORMAT=DYNAMIC;


And this is the query I want to run together with the servers
explaination:

mysql> EXPLAIN EXTENDED SELECT a.source, s.stockspam, s.images,
count(*)
-> FROM spam_archive a JOIN spam_plaintext s ON a.md5=s.msghash
-> GROUP BY source, stockspam, images\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: s
type: index
possible_keys: MSGHASH
key: MSGHASH
key_len: 38
ref: NULL
rows: 1699942
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: HASH
key: HASH
key_len: 34
ref: spam.s.msghash
rows: 1
Extra:
2 rows in set, 1 warning (0.00 sec)

spam_archive cover 1.7 million rows and 11 gb of data
spam_plaintexts cover the same number of rows with 4 gb of data

Both tables use the MyISAM engine, and the MySQL version is 5.0.37
My system has 2GB of RAM, for MySQL there are 768MB key buffer and
256MB sort buffer

I would appreciate any , why this query takes so long.

Thanks and regards,
Christoph

On 2007-09-19, Christoph Krammer <redtiger84@googlemail.com> wrote:
> Hello everybody,
>
> I have two rather large table with TEXT fields where I need to do a
> JOIN. All fields that are used in the JOIN and should be part of the
> result are covered in an index, but the JOIN still uses temporary
> tables and takes three hours (!) to complete.
>
> This is my schema:

<snip>

Try forcing the index use:
http://dev.mysql.com/doc/refman/5.0/en/index-hints.html