Tabellenübergreifender Index

[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

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.

[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.