Tabellenübergreifender Index

[idefix2]

Ich würde gerne wissen, ob es irgendwie möglich ist, in Firebird einen Index anzulegen, der aus Spalten in mehreren Tabellen besteht, um eine Abfrage zu beschleunigen, die ein join aus diesen Tabellen sortiert anzeigen soll. Ich suche jetzt schon eine ganze Weile in der Firebird Dokumentation, aber ich finde nichts dazu.

[fkerber]

Hi!

Warum sollte das ein Index sein?
Es sollte ja eher auf jeder der beiden Spalten ein Index liegen, oder?


Liebe Grüße,
Frederic

[idefix2]

Tabelle Musik - Felder Titel und IP_ID
Tabelle Interpret - Felder id und Name
Ich habe zwar die Tabelle Interpret auch nach dem namen indexiert, aber ein

Code:

select titel, interpret.name from musik join interpret on musik.ip_id=interpret.id order by interpret.name, titel

scheint den zweiten Index nicht wirklich zu verwenden - zumindest wird das Zeug extrem langsam und im Plan, den die Database Workbench anzeigt, kommt der Index nicht vor.
Ich habe jetzt versucht, das über ein berechnetes Feld in der Tabelle Musik zu lösen, aber auch berechnete Felder werden im Index nicht zugelassen.

[mkinzler]

Du benötigst einen Index mit id und name

[idefix2]

meinst Du interpret.id, interpret.name.

Was sollte das für einen Sinn haben? Id ist eindeutig, und Name würde an der sortierung dieses Index nichts ändern

[mkinzler]

Ich rede von den Feldern der Interpret-Tabelle.

[idefix2]

ok, und was erreiche ich mit einem Index Interpret.id, Interpret.name?

Id ist ein eindeutiger Wert in der tabelle Interpret. Zwei Felder bei einem Index anzugeben, hat, so viel ich weiss, nur Sinn, wenn das erste Feld allein die Datensätze nicht eindeutig identifiziert. Dann dient das zweite Feld zur Sortierung der Ergebniszeilen in jeder Gruppe, in der das erste Feld gleiche Werte hat.

[mkinzler]

ID, da es ein Fremdschlüssel ist und Nmae für die Einschränkung durch where

[idefix2]

Code:

select titel, interpret.name from musik join interpret on musik.ip_id=interpret.id order by interpret.name, titel

Da ist kein where. Ich will alle Datensätze, aber sortiert. Die Sortierung soll sein: interpret.name, musik.titel
Ein Index für interpret.id ist natürlich für den join wichtig. Aber id ist ohnehin primary key in der Tabelle Interpret, also gibt es den Index schon. Für das Sortieren nach Name würde ein Index id,name überhaupt nichts beschleunigen, weil er ja in 1. Linie nach id sortieren würde, und nur innerhalb gleicher id nach Name, wenn id nicht eindeutig wäre.

[haentschman]

Guten Morgen...

nur mal so am Rande. Was für einen Geschwindigkeitsvorteil erhoffst du dir ? Um wieviele Datensätze handelt es sich denn ?

[idefix2]

Insgesant sind in der Tabelle ca 120000 Datensätze, die folgenden Queries liefern davon ca 55000

SQL-Code:

select * from musik 
where (titel like '% %') and (Titel>'lo')
order by titel

SQL-Code:

select * from musik 
where (titel like '% %') and (Titel>'lo')
order by titel,ip_name

ip_name ist jetzt ein berechnetes feld in der tabelle Musik, das ist immer noch um einiges schneller als ein joint der beiden Tabellen.

Die erste Abfrage liefert die ersten 20 Datensätze innerhalb von weniger als 1 Sekunde, ein Sprung ans Ende des Ergebnis-Sets dauert ca 4 Sekunden.
Die zweite Abfrage liefert die ersten 20 Datensätze innerhalb ca 35 Sekunden, ein Sprung ans Ende des Ergebnis-Sets dauert noch einmal ca 35 Sekunden.

Der einzige Unterschied ist die zusätzliche Sortierung nach dem Feld IP_Name, das ich, weil es ein berechneter Wert ist, nicht in den Index einbeziehen kann.

Natürlich kann ich zur Not eine Shadow-Spalte in der Tabelle Musik einrichten und über Trigger füllen, aber das kann es ja wohl nicht sein?

[haentschman]

Zitat:

Die zweite Abfrage liefert die ersten 20 Datensätze innerhalb ca 35 Sekunden

:shock:

wobei ich fast dazu tendieren würde, daß das like dafür verantwortlich ist. Denn, wenn ich es noch richtig in Erinnerung habe, greift beim like der Index sowieso nicht.

Zitat:

ein Sprung ans Ende des Ergebnis-Sets

bedeutet was ?

[DeddyH]

Nicht ganz richtig. Das gilt nur dann, wenn der Suchbegriff mit einer Wildcard beginnt, dann kann der Index nicht mehr genutzt werden.

[haentschman]

Zitat:

Zitat von DeddyH (Beitrag 1030213)

Nicht ganz richtig. Das gilt nur dann, wenn der Suchbegriff mit einer Wildcard beginnt, dann kann der Index nicht mehr genutzt werden.

da hätten wir

Code:

titel like '% %'

Wildcard vorn und hinten

PS: DeddyH hat Recht... wie immer 8-)

[DeddyH]

Das ist ja auch schnell erklärt. Nehmen wir mal an, wir sollen im Telefonbuch manuell nach Namen suchen (nehmen wir mal "Müller"). Heißt die Aufgabenstellung "suche mir alle Namen, die mit Müller beginnen", geht man ja so vor:
- Gehe zu "M", dann weiter zu "Mü", dann zu "Mül" usw.
- nun haben wir alle gesuchten schön untereinander stehen und können sie rausschreiben

Ändert man aber die Aufgabe in "suche mir alle, die Müller enthalten", bleibt einem nichts anderes übrig, als bei "A" zu beginnen und alle Einträge der Reihe nach zu untersuchen.

[idefix2]

Nein.

Die Abfragen 1 und zwei lauten genau gleich, abgesehen von der Sortierung.
Die bedingung like... ist in beiden Abfragen enthalten, nur wenn ich in der Sortierung zusätzlich zu Titel das Feld IP_NAME angebe, wird die Query so langsam. Und IP_NAME hat mit dem like nichts zu tun.

Habe jetzt zum Überprüfen die like Klausel in beiden Abfragen weggelassen, es ändert nichts am Zeitverhalten (über diese Klausel wurden nur die Datensätze selektiert, die mehr als ein Wort im Titel haben, und das sind fast alle - Der Query Optimizer behandelt das offenbar richtig).

Zitat:

ein Sprung ans Ende des Ergebnis-Sets
bedeutet was ?

Nach Eingabe des SQL Befehls werden die ersten ca 15 Datensätze angezeigt (die auf den Schirm passen), danach springe ich ans Ende zu letzten 15 Datensätze (von insgeasamt ca 55000)

[Sir Rufo]

Zitat:

Zitat von idefix2 (Beitrag 1030225)

Nein.

Die Abfragen 1 und zwei lauten genau gleich, abgesehen von der Sortierung.
Die bedingung like... ist in beiden Abfragen enthalten, nur wenn ich in der Sortierung zusätzlich zu Titel das Feld IP_NAME angebe, wird die Query so langsam. Und IP_NAME hat mit dem like nichts zu tun.

Ja, aber wie hast du dir denn gedacht, soll der SQL-Server diese Sortierung durchführen?

IP_NAME ist ein berechnetes Feld ... also muss der SQL-Server zum Sortieren die ganze Tabelle aufbauen, dann Sortieren und kann Dir erst dann die Ergebnisse liefern.

Mich wundert es daher nicht, dass es erheblich länger dauert, wenn die Sortierung von IP_NAME hinzukommt.

[idefix2]

Es WUNDERT mich nicht allzusehr.

Wenn tabellenübergreifende Indizes oder berechnete Werte in Indizes möglich wären, würde es da kein Problem geben. Die Aufgabenstellung ist ja nicht wirklich sehr exotisch. Je mehr man sich bei der Datenbankdefinition der Normalform nähert, desto mehr Tabellen und damit Joins oder eben berechnete Spalten aus anderen Tabellen bekommt man. Wenn sich das so extrem auf die Performance niederschlägt, muss etwas faul sein.

Wobei ich eher annehme, dass es dafür eine Lösung gibt, und dass ich nur nicht weiss, wie sie aussieht. Deshalb dieser Thread.

[DeddyH]

Tja, mir fällt im Moment auch nichts mehr ein, Du könntest höchstens noch hier schauen: http://www.firebirdfaq.org/cat6/

[mkinzler]

Vielleicht wäre es eine Option das Feld nur für die Ergebnismenge und nicht für die gesamte Tabelle berechnen zu lassen

[haentschman]

...in Verbindung mit kleineren Ergebnismengen (nur das holen was wirklich gebraucht wird)

Zitat:

Wenn sich das so extrem auf die Performance niederschlägt, muss etwas faul sein.

nicht immer die Fehler nur bei anderen suchen.

[Sir Rufo]

Ansonsten muss eine Tabelle erstellt werden, die alle Ausgabefelder (und die IDs) beinhaltet und mittels Trigger gefüllt wird.
In dieser Tabelle kann dann der Index entsprechend den Sortierkriterien angepasst werden.

Habe das mal spaßeshalber mit MySQL durchgespielt und die Antwortzeiten liegen beim JOIN-Select bei ca. 1.5 Sekunden und mit der Hilfstabelle bei 0.15 Sekunden.

[xZise]

Wie verhält sich denn die Sortierung, wenn du es manuell machst?

Und eine Begründung könnte sein: Bei einer Sortierung geht Quicksort bei mehreren aber nur Mergesort (es war doch so, dass man bei order by die Sortierpriorität einstellte oder? Zuerst nach A dann darin alle nach B sortieren).

MfG
Fabian

[Sir Rufo]

Die Änderung Sortierpriorität ergab bei MySQL keine anderen Zeitwerte.
Auch ein Index auf musik.titel und/oder interpret.name brachte da nix.

Grund: MySQL legte die Abfrage in eine temp. Tabelle (bei 120000 Einträgen in Musik wurde die auch noch auf die Platte ausgelagert) und dann sortiert.
Die Sortierung nimmt dann ca. 60-70% der gesamten Abfragezeit in Anspruch.

Die zusammengeführte und über Trigger/Foreign-Key aktualisierte Tabelle mit den Ausgabewerten und natürlich den passenden Indizes für die gewünschten Sortierungen (IDX1=name,titel; IDX2=titel,name) ergibt trotz der 120000 Datensätze ein sehr schnelles Ergebnis.

Ist zwar etwas Daten-Overhead aber meines Wissen nicht anders zu lösen, wenn es auf absolute Performance ankommt.

[idefix2]

Zitat:

Vielleicht wäre es eine Option das Feld nur für die Ergebnismenge und nicht für die gesamte Tabelle berechnen zu lassen

Zitat:

in Verbindung mit kleineren Ergebnismengen (nur das holen was wirklich gebraucht wird)

Kleinere Ergebnismengen bekomme ich durch Angabe einer rows Klausel (entspricht Limit in mssql). Leider beschleunigt das den Vorgang nicht wesentlich.

Dank des Hinweises von DeddyH bin jetzt draufgekommen, dass man Indizes seit Firebird 2 doch auch nach Fremdtabellen berechnen kann (über computed by an Stelle einer Spaltenangabe). Allerdings beschleunigt das den Vorgang auch nicht nennenswert, obwohl ich nur die ersten 20 abrufe und ein Index vorhanden ist, geht Firebird über die ganze Tabelle.

Zitat:

nicht immer die Fehler nur bei anderen suchen.

Nicht aus dem Zusammenhang gerissen zitieren, würde ich sagen. Ich habe im nächsten Satz geschrieben:
Wobei ich eher annehme, dass es dafür eine Lösung gibt, und dass ich nur nicht weiss, wie sie aussieht.

Zitat:

Ansonsten muss eine Tabelle erstellt werden, die alle Ausgabefelder (und die IDs) beinhaltet und mittels Trigger gefüllt wird.

Das verstehe ich jetzt nicht. Welcher Trigger (ich meine auf welches Ereignis hin)? Meinst Du eine temporäre Tabelle, die Du vor der Abfrage füllst, oder führst Du eine Tabelle permanent parallel mit? - da wäre eine trigger-befüllte Shadow-Spalte InterpretName statt der berechneten Spalte in der Tabelle Musik noch billiger. So geht es natürlich und bringt eine akzeptable Performance, ist aber meines Erachtens extrem "unschön".

[sx2008]

Zitat:

Zitat von idefix2 (Beitrag 1030115)

... einen Index anzulegen, der aus Spalten in mehreren Tabellen besteht

Ein Index ist immer an eine bestimmte Tabelle gebunden.
Oder wenn man es verallgemeinert: ein Index ist immer an ein Recordset gebunden.

Bei manchen DBMS (z.B. MS SQL Server) ist es auch möglich Indexe auf Views zu legen.
Damit kann man einen Index als Verbund aus zwei oder mehr Felder aus verschiedenen Tabellen aufbauen.

Rein theoretisch könnte man sich vorstellen, Indexe tabellenübergreifend zu erstellen.
Aber man braucht dazu nicht nur die Tabellennamen sondern auch die relationale Beziehung der Tabellen.

Hier mal der Versuch, diesen Index als SQL auszudrücken:

Code:

CREATE INDEX Test (TabelleA.Feld1, TabelleB.Feld2) TabelleA INNER JOIN TabelleB ON TabelleA.PKFeldA=TabelleB.PKFeldB