MySQL, Indices beim Kopieren

[Harald]

Hallo,

ich habe zwei Tabellen mit der gleichen Struktur.
Jedoch hatte eine Tabelle ein Index auf einem Feld, was dort wichtig ist. Die andere Tabelle hat das nicht, weil unwichtig.

Nun werden Daten von der Tabelle ohne Index in die mit dem Index verschoben (kopieren und löschen).
Ist es für die Geschwindigkeit erheblich, ob ein Index schon besteht oder wird der beim INSERT sowieso erst erstellt?

Danke!

Harald

[Philipp Hasenfratz]

Halihallo Harald

Ist es für die Geschwindigkeit erheblich, ob ein Index schon besteht oder wird der beim INSERT sowieso erst erstellt?

Wenn du einen neuen Datensatz in eine Tabelle mit Indizies einfügst,
werden die Indizies auch erweitert (erstellt werden sie ja beim
CREATE TABLE). Mit anderen Worten: Du kannst in eine Tabelle ohne
Indizies schneller einfügen, als wenn sie einen Index hat. Dafür ist
die Tabelle ohne Indizies bei vielen Abfragen sehr viel langsamer
wenn es um das Selektieren von Daten geht...
Wenn du mich frägst, ob du den Index vor oder nach dem Einfügen der
Datensätze erstellen sollst:

Nun, das Einfügen mit Index dauert länger, als wenn der Index später
über den ganzen Datenbestand gebildet wird. Zudem könnte ich mir gut
vorstellen, dass ein nachträglich hinzufügter Index ausgeglichener
ist und somit noch etwas Performance beim selektieren der Datensätze
gewonnen werden kann, aber dies würde ich als "nicht bemerkbar, da
fast kein Unterschied" einstufen.

Fazit:
  a) Erstelle den Index erst nachdem alle Daten in die Tabelle
     eingefügt werden, falls dies ohne grössere Umstände möglich
     ist.
  b) Ein Index macht SELECT schneller, INSERT aber langsamer.

Viele Grüsse

Philipp

[Harald]

Hallo,

Fazit:
  a) Erstelle den Index erst nachdem alle Daten in die Tabelle
     eingefügt werden, falls dies ohne grössere Umstände möglich
     ist.
  b) Ein Index macht SELECT schneller, INSERT aber langsamer.

danke!
Aber Du hast mich falsch verstanden.

Tab-A HAT einen Index auf Feld X.
TAB-B hat KEINEN Index auf Feld X.

Nun mache ich:

insert into TAB-A select * from TAB-B;

Wäre es besser gewesen, TAB-B hätte vor dem Befehl auch schon einen Index besessen?

Gruß
Harald

[Philipp Hasenfratz]

Halihallo Harald

Tab-A HAT einen Index auf Feld X.
TAB-B hat KEINEN Index auf Feld X.
insert into TAB-A select * from TAB-B;
Wäre es besser gewesen, TAB-B hätte vor dem Befehl auch schon einen Index besessen?

Nein. Bei SELECT * ohne WHERE (+Co) bringt ein Index nichts... Dieser
zweite Teil des Queries (SELECT...) wird durch einen Index weder
schneller noch langsamer.
Aber der Index von Tab-A verlangsamt den INSERT, da bei jedem
Datensatz der Index überarbeitet werden muss (es muss ja eine neue
Referenz eingefügt werden). Besser (was heisst besser? - Es wäre
einfach etwas schneller :-)) wäre also, den Index von Tab-A erst
nach dem Einfügen aller Daten zu erstellen.

Viele Grüsse

Philipp

[Harald]

Nein. Bei SELECT * ohne WHERE (+Co) bringt ein Index nichts... Dieser
zweite Teil des Queries (SELECT...) wird durch einen Index weder
schneller noch langsamer.
Aber der Index von Tab-A verlangsamt den INSERT, da bei jedem
Datensatz der Index überarbeitet werden muss (es muss ja eine neue
Referenz eingefügt werden). Besser (was heisst besser? - Es wäre
einfach etwas schneller :-)) wäre also, den Index von Tab-A erst
nach dem Einfügen aller Daten zu erstellen.

ok, verstanden!
Aber ist es ein Zeitgewinn, wenn ich hinterher den Index erstelle?
Ist doch nur nach hinten verlagert, oder?

Sonst müßte ich ja den Index löschen, Tab kopieren und dann Index wieder erstellen. Ich glaube, das ist dann sogar noch langsamer.

Gruß
Harald

[wahsaga]

hi,

Aber ist es ein Zeitgewinn, wenn ich hinterher den Index erstelle?
Ist doch nur nach hinten verlagert, oder?

Sonst müßte ich ja den Index löschen, Tab kopieren und dann Index wieder erstellen. Ich glaube, das ist dann sogar noch langsamer.

ich glaube, ihr redet ein wenig aneinander vorbei.
Philipp hat dich wohl zu verstanden, dass du von einer einmaligen umkopier-aktion redest - dann ist sein vorgehen natürlich vorzuziehen, weil einfach performanter.

aber deine nachfrage klingt mir jetzt so, als ob du da regelmässiger daten von der einen in die andere tabelle kopieren willst ...?

gruß,
wahsaga

[Harald]

hi,

aber deine nachfrage klingt mir jetzt so, als ob du da regelmässiger daten von der einen in die andere tabelle kopieren willst ...?

genau, wenn auch nicht alle drei Minuten!

Gruß
Harald

[Harald]

aber deine nachfrage klingt mir jetzt so, als ob du da regelmässiger daten von der einen in die andere tabelle kopieren willst ...?

genau, wenn auch nicht alle drei Minuten!

Aber, was macht es für einen Unterschied, wie oft ich das machen will.
Ich rede sowieso nicht vom Sekundentakt! :-)
Aber selbst, wenn ich das einmal am Tag mache - das ist sowieso eher vom Verhältnis Datenmenge/Zeit abhängig -, denke ich, es dauert länger, den Index zu löschen, um beim Insert einen Vorteil zu haben und ihn dann aber wieder (den Vorteil) kaputt zu machen, indem ich den Index wieder setze.
Das würde dann stimmen, wenn ich mehr Interesse an einem NEUEN Index habe, oder?

Gruß
Harald

[Philipp Hasenfratz]

Halihallo Harald

Aber selbst, wenn ich das einmal am Tag mache - das ist sowieso eher vom Verhältnis Datenmenge/Zeit abhängig -, denke ich, es dauert länger, den Index zu löschen, um beim Insert einen Vorteil zu haben und ihn dann aber wieder (den Vorteil) kaputt zu machen, indem ich den Index wieder setze.

Teste dies mal mit 1'000'000 Datensätzen und staune :-)

Das würde dann stimmen, wenn ich mehr Interesse an einem NEUEN Index habe, oder?

Dann hast du einfach noch mehr Zeit gespart :-)
Es hilft jedoch auch in deinem Fall.

---

Grundsätzlich: Die "Index löschen, Daten einfügen, Index erstellen"-
Technik bringt wohl erst ab einer gewissen Anzahl an Datensätzen
etwas.
Deine Argumentation ist zwar einleuchtend, aber falsch :-)
Falsch deswegen, weil es vergleichsweise ein kleinerer Aufwand ist
den Index zu löschen und in einem Aufwisch neu zu erstellen, denn
wenn die Datenbank bei *jedem* INSERT eine "Referenz/Index" in den
Index schreiben muss (wenn der Index am Ende generiert wird, werden die  Daten in einem Aufwisch geschrieben => viel weniger
Schreibzugriffe auf die Festplatte). Falls dich diese Argumentation
nicht überzeugt, überzeugt dich ein Benchmark mit einer grossen
Tabelle (bei kleinen Tabellen könnte es eben auch kontraproduktiv
sein, das ist die "andere Seite der Münze").
Wo die Grenze liegt, kann ich dir nicht sagen. Das musst du selber
auf deinem System austesten.
Tipp: Teste es doch einfach mal :-)

BTW: Wenn es nicht darum geht eine ganze Tabelle zu
kopieren/verschieben, sondern nur einige Datensätze, dann gilt diese
Technik nur bedingt... Aber dein SQL-Statement mit
INSERT INTO ... SELECT * ... lässt dies nicht vermuten. Du kopierst
die gesamte Tabelle, ja?

Viele Grüsse

Philipp

[Harald]

DANKE, ich werde das für meinen Fall mal testen.

Ich denke, beide Ansätze haben Ihre Daseinsberechtigung!

Gruß
Harald

[Harald]

Ich habe nochwas besseres:

Ich kopiere TAB-B erst in TAB-A, wenn TAB-A leer ist und leere dann TAB-B. (Verschiebung der Daten)

Wenn ich Dich richtig verstehe, muß das performanteste ja sein, TAB-A zu löschen, TAB-B in TAB-A umzubenennen und dann eine neue TAB-B zu erstellen, oder?

Gruß
Harald

[Philipp Hasenfratz]

Halihallo Harald

Ich kopiere TAB-B erst in TAB-A, wenn TAB-A leer ist und leere dann TAB-B. (Verschiebung der Daten)
Wenn ich Dich richtig verstehe, muß das performanteste ja sein, TAB-A zu löschen, TAB-B in TAB-A umzubenennen und dann eine neue TAB-B zu erstellen, oder?

Ich verstehe nicht, inwiefern dies jetzt ein Vorteil sein sollte?
So wie ich das sehe, müsste folgendes getan werden:

DELETE TABLE Tab-A;
CREATE TABLE Tab-A (
   ...
   // ohne Index!
);
INSERT INTO Tab-A SELECT * FROM Tab-B;
ALTER TABLE Tab-A ... // Index hinzufügen

fertisch... Warum umbenennen und kopieren? - Du brauchst nur zu
kopieren...

Aber:
Teste erstmal, ob dies auch Performance bringt! - Es ist - wie schon
gesagt - nicht immer so. Vielleicht bringt es dir mehr
Kopfzerbrechen für weniger Leistung!

Viele Grüsse

Philipp

[Harald]

Hi,

fertisch... Warum umbenennen und kopieren? - Du brauchst nur zu
kopieren...

weil umbenennen numal sehr viel schneller geht. Auf Filesystemebene ist das ein Verzeichnis.

Gruß
Harald

[Ilja]

yo,

weil umbenennen numal sehr viel schneller geht. Auf Filesystemebene ist das ein Verzeichnis.

man sollte sich überlegen, ob man dinge nicht lieber einfach hält, anstelle lange an der falschen schraube zu drehen, die letztlich nicht wirklich etwas bringen. was phillip meint ist, dass durch das einfügen von einer größeren anzahl von datensätzen in eine tabelle durch einen index zusätzlich sehr viel zeit vergehen kann. ob man nun vorher die tabelle löscht, umbenennt oder durch einen truncate befehl leert ist sekundär und wird sich nicht bemerkbar machen.

wichtig ist nur, wenn du große mengen in eine tabelle einfügst, index löschen (eventuell deaktivieren) und danach neu erstellen oder on the fly reorganiseren. und genau das wird dir zeit sparen und nicht das umbenennen.

Ilja