Table Lookup MYSQL

Hallo zusammen,

ich habe ein Problem wofür ich im Internet keine schnelle Lösung
gefunden habe. Ich habe eine große Tabelle (ca. 20 Mio Linien / 3
Spalten: ID1, ID2, wert / keinerlei Index oder Key) und möchte daraus
nun die Zeilen behalten, deren ID1 in einer anderen Tabelle (6 Zeilen /
nur eine Spalte: ID1) vorkommt. Also ein klassischer Table Look-up.

Das Ganze dauert nun fast mehr als 4 Minuten mit einem klassischen:

SELECT * FROM tabelle1 WHERE id1 IN (SELECT id1 FROM tabelle2);

Daher nun meine Frage: was gibt es für Möglichkeiten das zu
beschleunigen?

Eine Möglichkeit obige Abfrage zu beschleunigen ist wohl ein Index in
der ersten Tabelle auf ID1, ID2. Das reduziert es von 239 Sekunden auf
194. Immerhin - obwohl es auch Cache sein könnte, oder?

Jedenfalls: gibt es da unter MySQL noch irgendwelche Tricks?

Das System ist ein MySQL 5.0.24 unter WindowsXP. Alle Datenbanken sind
InnoDB.

Für Tips wäre ich dankbar.

Gregor

Re: Table Lookup MYSQL

kroec schrieb:

Wer?

>
> Das Ganze dauert nun fast mehr als 4 Minuten mit einem klassischen:
>
> SELECT * FROM tabelle1 WHERE id1 IN (SELECT id1 FROM tabelle2);
>
> Daher nun meine Frage: was gibt es für Möglichkeiten das zu
> beschleunigen?
>
> Eine Möglichkeit obige Abfrage zu beschleunigen ist wohl ein Index in
> der ersten Tabelle auf ID1, ID2. Das reduziert es von 239 Sekunden auf
> 194. Immerhin - obwohl es auch Cache sein könnte, oder?
>
> Jedenfalls: gibt es da unter MySQL noch irgendwelche Tricks?
>

EXPLAIN

und dann natürlich geeignete Indizes. Dass es diesen Hinweis im
Internet nicht schon gegeben haben sollte, ist eine Lüge.

Re: Table Lookup MYSQL

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Table Lookup MYSQL

Hi,

Christian Kirsch schrieb:

> kroec schrieb:
>
> Wer?

Sorry, irgendwie war der Klarname im Google wieder weg.

> > Das Ganze dauert nun fast mehr als 4 Minuten mit einem klassischen:
> >
> > SELECT * FROM tabelle1 WHERE id1 IN (SELECT id1 FROM tabelle2);
> >
> > Daher nun meine Frage: was gibt es für Möglichkeiten das zu
> > beschleunigen?
> >
> > Eine Möglichkeit obige Abfrage zu beschleunigen ist wohl ein Index in
> > der ersten Tabelle auf ID1, ID2. Das reduziert es von 239 Sekunden auf
> > 194. Immerhin - obwohl es auch Cache sein könnte, oder?
> >
> > Jedenfalls: gibt es da unter MySQL noch irgendwelche Tricks?
> >
>
> EXPLAIN

Ich dachte da an spezielle MySQL Funktionen zu diesem Zwecke,
irgendwelche schmutzigen Dinge (in SAS kann man sowas auf 1000 Arten
ua. mit Formaten machen), InnoDB Besonderheiten, etc.

> und dann natürlich geeignete Indizes. Dass es diesen Hinweis im
> Internet nicht schon gegeben haben sollte, ist eine Lüge.

Naja, daher schrieb ich auch, dass ich ID1 und ID2 als Index definiert
habe.

Vielen Dank für die schnelle Antwort.

Gregor

Re: Table Lookup MYSQL

Gregor Börner wrote:

>> EXPLAIN
>
> Ich dachte da an spezielle MySQL Funktionen zu diesem Zwecke,
> irgendwelche schmutzigen Dinge (in SAS kann man sowas auf 1000 Arten
> u.a. mit Formaten machen), InnoDB Besonderheiten, etc.

EXPLAIN erklärt dir, wieviele Verknüpfungen Dein Query ausführt. Das
Handbuch verrät Dir, was die Ausgabe verrät und wie Du es besser machen
kannst.

In Deinem konkreten Fall macht es wohl durchaus Sinn, die Spalten id1
jeweils zu indizieren.

Aber wozu soll das Subquery gut sein? Was spricht gegen

SELECT tabelle1.* FROM tabelle1 JOIN tabelle2
USING (id1) /* oder: ON tabelle1.id1=tabelle2.id1 */

?

Wobei Du im Produktivbetrieb auf tabelle1.* verzichten solltest, sondern
vielmehr jede einzelne Spalte benennen solltest.


> Naja, daher schrieb ich auch, dass ich ID1 und ID2 als Index definiert
> habe.

Du schriebst:

| ca. 20 Mio Linien / 3 Spalten: ID1, ID2, wert / keinerlei Index oder Key


Thomas
--
Dieses Pfefferminzbonbon schmeckt ja scheußlich!
Da war sicher ein Falschminzer am Werk.

Re: Table Lookup MYSQL

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Table Lookup MYSQL

"kroec" wrote:
> Hallo zusammen,
>
> ich habe ein Problem wofür ich im Internet keine schnelle Lösung
> gefunden habe. Ich habe eine große Tabelle (ca. 20 Mio Linien / 3
> Spalten: ID1, ID2, wert / keinerlei Index oder Key) und möchte daraus
> nun die Zeilen behalten, deren ID1 in einer anderen Tabelle (6 Zeilen /
> nur eine Spalte: ID1) vorkommt. Also ein klassischer Table Look-up.
>
> Das Ganze dauert nun fast mehr als 4 Minuten mit einem klassischen:
>
> SELECT * FROM tabelle1 WHERE id1 IN (SELECT id1 FROM tabelle2);
>
> Daher nun meine Frage: was gibt es für Möglichkeiten das zu
> beschleunigen?

Kommt darauf an. 20 Mio rows zu je 10 Byte (kommt das hin?) wären
200MB. Bei aktueller Hardware paßt das mehr oder weniger problemlos
in den Hauptspeicher.

> Eine Möglichkeit obige Abfrage zu beschleunigen ist wohl ein Index in
> der ersten Tabelle auf ID1, ID2.

Für *diese* Query reicht ein Index auf ID1.

> Das reduziert es von 239 Sekunden auf
> 194. Immerhin - obwohl es auch Cache sein könnte, oder?

Das ist natürlich immer *auch* Cache. Für vergleichbare Meßbedingungen
mußt du schon selber sorgen.

Allerdings bringt ein Index auf ID1 nichts, wenn sehr viele Rows aus
tabelle1 getroffen werden. Für das SELECT * müssen die Rows ja nicht
nur gefunden werden, sondern auch gelesen. Deswegen verwendet der
Optimizer den Index nur dann, wenn er schätzt, daß nicht mehr als 50%
der Tabelle gelesen werden muß (das ist jetzt bewußt vereinfacht, in
Wirklichkeit beachtet der Optimizer noch mehr).

K.A. ob das hier der Fall ist. Aber auch dann kommen mir 200 sec für
den notwendigen Tablescan sehr lang vor.

> Jedenfalls: gibt es da unter MySQL noch irgendwelche Tricks?
>
> Das System ist ein MySQL 5.0.24 unter WindowsXP. Alle Datenbanken sind
> InnoDB.

Der "Trick" besteht auch bei MySQL darin, passende Indizes zu setzen.
In Verbindung mit InnoDB sollte man auch innodb_buffer_pool_size groß
genug wählen.

Was du auf jeden Fall machen solltest, ist EXPLAIN .
Je einmal mit und ohne Index auf ID1. Und testweise auch mal SELECT ID1
statt SELECT *. Wenn ersteres wesentlich schneller ist, dann dauert das
Lesen der Daten so lange.


XL