Tabellenvergleich performance

[mikexxl]

Hallo,
habe mal eine echte Performancefrage zum Tabellenvergleich
Ausgangsituation:
Tabelle1 mit spalten a,b,x
Tabelle2 mit splaten g,h,i,j,x
In die Zieltabelle3 (g,h,i,j,x) sollen alle aus Tabelle2, die nicht in Tabelle1 sind.
Abgleich über x

Habe nun folgendes gemacht:
INSERT INTO `db`.`tab3`
SELECT *
FROM `db`.`tab2`
WHERE `x` not IN (SELECT x FROM `db`.`tab1`);

Es kam keine Fehlermeldung, allerding weiß ich nicht, was nun passiert. Der Prozess läuft, Time ist 339826.
Habe ich da schon einen Syntaxfehler (ein x ist ohne `)?
Die Herausforderung, beide Tabellen haben über 13 Mio Datensätze.

Nun muss ich das Beschleunigen:
System ist 64bit Prozessor, 4 GB Ram, Debian 64bit
MySQL ist die aktuelle LAMPP version, ist nur 32 bit. Evtl hier auf MySQL 5.5 64bit updaten (wie/howto)?
Ich benötige das LAMPP Paket eigentlich nicht, da ich via SSH auf der mysql-Konsole die Befehle eingebe.
Evtl. später, um die Befehle über eine Weboberfläche zu händeln.
Anstelle des where ein Join Befehl (danke für die Übersicht hier im Forum, habe es nun begriffen, super)?
Nun habe ich irgendwo schon gelesen, daß ich Tabelen sperren kann, Index muss nicht erstellt werden usw, wer hat da Erfahrung?

Danke für Eure Hilfe
MikeXXL

[thomas_w]

Zeige doch mal die Tabellenstrukturen

mysql>SHOW CREATE TABLE tab1;
mysql>SHOW CREATE TABLE tab2;

Folgendes ist auf jeden Fall falsch, da hier die Konstante 'x' mit der Spalte X verglichen wird.

..
WHERE `x` not IN (SELECT x FROM `db`.`tab1`);
..

KORREKTUR

Könnte doch richtig sein, da es sich ja um die Backticks und nicht um einfache Anführungsstriche handelt.

..
WHERE `x` not IN (SELECT x FROM `db`.`tab1`);
..

Grüße
Thomas

[mikexxl]

Hier die Tabellen:
| tab1 | CREATE TABLE `tab1` (
`a` text COLLATE utf8_unicode_ci NOT NULL,
`b` text COLLATE utf8_unicode_ci NOT NULL,
`x` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |


| tab2 | CREATE TABLE `tab2` (
`g` text COLLATE utf8_unicode_ci NOT NULL,
`h` text COLLATE utf8_unicode_ci NOT NULL,
`i` text COLLATE utf8_unicode_ci NOT NULL,
`j` text COLLATE utf8_unicode_ci NOT NULL,
`k` text COLLATE utf8_unicode_ci NOT NULL,
`l` text COLLATE utf8_unicode_ci NOT NULL,
`m` text COLLATE utf8_unicode_ci NOT NULL,
`n` text COLLATE utf8_unicode_ci NOT NULL,
`o` text COLLATE utf8_unicode_ci NOT NULL,
`p` text COLLATE utf8_unicode_ci NOT NULL,
`q` text COLLATE utf8_unicode_ci NOT NULL,
`r` text COLLATE utf8_unicode_ci NOT NULL,
`s` text COLLATE utf8_unicode_ci NOT NULL,
`x` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

MikeXXL

[thomas_w]

Zitat von mikexxl

..
`x` text COLLATE utf8_unicode_ci NOT NULL
..

Au weia, Du willst TEXT Spalte miteinander vergleichen.

Ermittel doch mal die max. Länge der X Spalten. Eventuell läßt sich da noch was reparieren.

SELECT MAX(length(x)) FROM tab1;
SELECT MAX(length(x)) FROM tab2;

Grüße
Thomas

[mikexxl]

Prima, hab es ja schon vermutet,
in tab 1 max 20
in tab 2 max 44

kann mir die 44 noch nicht erklären, normal wären auch so ca 20
seis drum

Gruß
MikeXXL

[thomas_w]

Zitat von mikexxl

in tab 1 max 20
in tab 2 max 44

Hast Du einen Backup der Tabellen, ja?

Also im ersten Schritt machen wir anstatt TEXT ein VARCHAR(50). Vorerst mal mit der Spalte x. (auch bei der Zieltabelle)

ALTER TABLE tab1
        CHANGE x x VARCHAR(50) NOT NULL  COLLATE utf8_unicode_ci ;
ALTER TABLE tab2
        CHANGE x x VARCHAR(50) NOT NULL  COLLATE utf8_unicode_ci ;

Wenn das durchläuft, legen wir mal einen Index an.

ALTER TABLE tab1
        ADD KEY idx_x (x);
ALTER TABLE tab2
        ADD KEY idx_x (x);

Grüße
Thomas

[mikexxl]

Muss ich dafür den vorhandenen Prozess beenden?

[thomas_w]

Zitat von mikexxl

Muss ich dafür den vorhandenen Prozess beenden?

Welchen Prozess? Die Datenbankengine muss natürlich laufen für die ALTER TABLE.

Grüße
Thomas

[mikexxl]

Naja, der Abgleich läuft,
wird angezeigt bei show prozesslist

habe beide tabellen kopiert, neue zieltabelle erstellt, x geändert, key definiert

[thomas_w]

Dann versuchen wir mal ersten Test

a) EXPLAIN um den Zugriffspfad zu kontrollieren

EXPLAIN
SELECT COUNT(*) 
  FROM tab2
  LEFT JOIN tab1
    ON tab1.x = tab2.x
 WHERE tab1.x IS NULL;

Die EXPLAIN-Ausgabe sieht so ähnlich aus. Die in der MySQL Console ausführen.

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql>

b) Treffer zählen und Zeit messen

SELECT COUNT(*) 
  FROM tab2
  LEFT JOIN tab1
    ON tab1.x = tab2.x
 WHERE tab1.x IS NULL;

EDIT
c) Dies wäre auch noch interessant:

mysql>show global variables LIKE '%buffer%';

Grüße
Thomas