Join über Index-Spalten nur mit temporärer Tabelle?

Hallo,

ich habe folgendes Problem mit zwei sehr großen Tabellen, über die ich
ein Query machen möchte:

spam_archive, 1.7 Mio Zeilen, Datenlänge 11,4 GB, Indexlänge 110 MB
(MyISAM)
spam_plaintext, 1.7 Mio Zeilen, Datenlänge 3 GB, Indexlänge 138 MB
(MyISAM)

Folgendes Query möchte ich ausführen:
SELECT a.source, s.stockspam, s.images, count(*)
FROM spam_archive a JOIN spam_plaintext s ON a.md5=3Ds.msghash
GROUP BY source, stockspam, images;

In spam_archive existiert ein UNIQUE BTREE INDEX über md5 sowie ein
BTREE INDEX (nicht unique) über source.
In spam_plaintext existiert ein UNIQUE BTREE INDEX über msghash,
stockspam und images.

Die Hash-Felder (md5, msghash) sind jeweils VARCHAR(32), alle anderen
Felder sind integer.

Allerdings führt MySQL (5.0.37) das Query nicht über die Indizies aus,
sondern über eine temporäre Tabelle. Der Server hat 768MB Key Buffer,
256MB Sort Buffer und auch bei den MyISAM Parametern habe ich jeweils
ordentlich Speicher eingetragen (Rechner hat 2GB RAM und tut sonst
nicht viel).

Das komplette kopieren in eine temporäre Tabelle dauert halt bei der
Datenmenge ewig. Kann mir jemand helfen, wie ich MySQL dazu bringe,
die Indizes im Speicher zu joinen?

Danke,
Christoph

Re: Join über Index-Spalten nur mit temporärer Tabelle?

Christoph Krammer wrote:
> Das komplette kopieren in eine temporäre Tabelle dauert halt bei der
> Datenmenge ewig. Kann mir jemand helfen, wie ich MySQL dazu bringe,
> die Indizes im Speicher zu joinen?

die CREATE TABLE Statements beider Tabellen und das EXPLAIN
Ergebnis der Abfrage wären hier echt hilfreich ...

--=20
hartmut

Re: Join über Index-Spalten nur mit temporärer Tabelle?

On 16 Sep., 17:02, Hartmut Holzgraefe wrote:
> die CREATE TABLE Statements beider Tabellen und das EXPLAIN
> Ergebnis der Abfrage wären hier echt hilfreich ...

Gerne:

-- 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=3DMyISAM AUTO_INCREMENT=3D1960857 DEFAULT CHARSET=3Dlatin1;

-- 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=3DMyISAM AUTO_INCREMENT=3D1768865 DEFAULT CHARSET=3Dlatin1
COLLATE=3Dlatin1_bin ROW_FORMAT=3DDYNAMIC;


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


Ich hoffe das hilft.

Christoph

Re: Join über Index-Spalten nur mit temporärer Tabelle?

Christoph Krammer schrieb:

> 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;
> +----+-------------+-------+--------+---------------+------- --
> +---------+----------------+---------
> +----------------------------------------------+
> | id | select_type | table | type | possible_keys | key |

Wenn Du das Statement nicht mit ';', sondern mit '\G' abschließt, wird
das Ganze auch lesbar.

Gruß. Claus

Re: Join über Index-Spalten nur mit temporärer Tabelle?

On 16 Sep., 20:15, Claus Reibenstein <4spammerso...@web.de> wrote:
> Wenn Du das Statement nicht mit ';', sondern mit '\G' abschließt, wird
> das Ganze auch lesbar.

mysql> EXPLAIN EXTENDED SELECT a.source, s.stockspam, s.images,
count(*)
-> FROM spam_archive a JOIN spam_plaintext s ON a.md5=3Ds.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)