Optimierung einer Abfrage eines Datensatzes samt Vorgänger und Nachfolger

Sirke · 27.04.2010, 23:38

Hallo zusammen,

Ich hoffe mal einen passenden Titel gefunden zu haben, auch wenn diese etwas lang geworden ist!

Ich habe eine ähnliche Tabelle wie diese:

Code:

CREATE TABLE `data` (
`id`    INT UNSIGNED  NOT NULL  AUTO_INCREMENT  PRIMARY KEY ,
`group` INT UNSIGNED  NOT NULL ,
`time`  TIMESTAMP     NOT NULL
);

Ähnlich heißt in diesem Fall, dass die Felder anders heißen und weitere Felder in der Tabelle existieren, welche jedoch für dieses Beispiel uninteressant sind. In dieser Tabelle speicher ich Datensätze in unterschiedlichen Gruppen mit dem Timestamp des Zeitpunktes des Erstellens ab, welcher als Sortierkriterium dient!

Nun möchte ich einen bestimmten Datensatz zusammen mit dem zeitlichen Vorgänger und dem Nachfolger in möglichst einer Abfrage erhalten. Früher hatte ich ein ähnliches Problem schon einmal, jedoch war das Sortierkriterium dort eine vorlaufende Nummer, sodass der Vorgänger durch +1 und der Nachfolger durch -1 ausgewählt werden konnte. In diesem Fall ist der Zeitpunkt des Vorgängers und des Nachfolgers jedoch nicht bekannt, sodass ich nicht weiß, wie ich diese Abfrage durchführen kann.

Habe ich nun zum Beispiel folgende Datensätze in der DB

Code:

INSERT INTO `data` (`id`, `group`, `time`) VALUES
(1, 1, '2010-01-01 01:23:00'),
(2, 1, '2010-02-01 04:56:00'),
(3, 1, '2010-03-01 07:23:00'),
(4, 1, '2010-04-01 09:47:00'),
(5, 1, '2010-04-03 02:01:00'),
(6, 1, '2010-04-05 03:39:00');

und möchte einen Datensatz samt Nachfolger und Vorgänger ermitteln, führe ich zur Zeit drei Abfragen nacheinander aus:

Code:

SELECT * FROM data WHERE id=4;

SELECT * FROM data WHERE time < $time AND group = $group time DESC LIMIT 1;
SELECT * FROM data WHERE time > $time AND group = $group time ASC  LIMIT 1;

wobei $time und $group von dem Datensatz der ersten Abfrage stammen!

Sobald ich nun eine Liste von N Datensätzen eine Gruppe abfragen möchte, benötige ich 2*N + 1 Abfragen für Vorgänger und Nachfolger, was etwas viel ist, wenn ich das womöglich in einer Abfrage erhalten könnte!

Gibt es eine Möglichkeit dies mit einer Abfrage durchzuführen oder benötige ich dafür zwingend ein Sortierkriterium, wo das des Vorgängers und das Nachfolgers bekannt ist?

Vielen Dank und ich hoffe meine Frage/mein Problem ist verständlich?

Grüße, Sirke

ChrisB · 28.04.2010, 00:11

Zitat:

Zitat von Sirke

möchte einen Datensatz samt Nachfolger und Vorgänger ermitteln, führe ich zur Zeit drei Abfragen nacheinander aus

Fein, dann verbinde die noch per UNION, wenn du nur „ein“ Statement haben willst, was vom Script aus abgesetzt und ausgewertet werden muss - und dann hast du das Optimum des Möglichen auch schon so gut wie erreicht.

(Es sei denn, du bräuchtest von den benachbarten Datensätzen nicht alle Spalten [Beispiele mit SELECT * sind doof - Anwendungen damit noch mehr

], sondern bspw. nur die ID für eine Verlinkung o.ä. - dann würde ich das eher über Subselects machen, die mir diese beiden IDs als zusätzlichen Pseudo-Spalten innerhalb des einen gesuchten Datensatzes liefern; das macht m.E. dann auch die weitere Verarbeitung übersichtlicher.)

28.04.2010, 07:30

Zitat:

Zitat von Sirke

Gibt es eine Möglichkeit dies mit einer Abfrage durchzuführen oder benötige ich dafür zwingend ein Sortierkriterium, wo das des Vorgängers und das Nachfolgers bekannt ist?

Vielen Dank und ich hoffe meine Frage/mein Problem ist verständlich?

Das Problem ist sehr schön dargestellt.

Hier eine mögliche Lösung mit einem SQL, wobei diese versagt, wenn zwei Datensätze eine identische "time" haben. Dann werden mehrere Vorgänger/Nachfolger ausgegeben. In dem SUB-Select werden die drei Kandidaten ermittelt.

Code:

SELECT * FROM data
 WHERE time IN (
                SELECT MIN(time) FROM data
                 WHERE time > ( SELECT time FROM data 
                                WHERE id = 4 )
                                
                UNION ALL 
                
                SELECT time FROM data
                 WHERE id = 4                
                
                UNION ALL
                
                SELECT MAX(time) FROM data
                WHERE time < ( SELECT time FROM data 
                                WHERE id = 4 )
               )
ORDER BY time;

+----+-------+---------------------+
| id | group | time                |
+----+-------+---------------------+
|  3 |     1 | 2010-03-01 07:23:00 |
|  4 |     1 | 2010-04-01 09:47:00 |
|  5 |     1 | 2010-04-03 02:01:00 |
+----+-------+---------------------+
3 rows in set (0.02 sec)

mysql>

Grüße
Thomas

Sirke · 28.04.2010, 09:12

Ich weiß, dass UNION und Sub-Selects möglich sind, aber führt das immer zu einem neuen Select und damit doch auch zu einer neuen (internen) Abfrage, oder?

@ChrisB: Okay, das mit dem * war eig nur für eine kurze Schreibsweise des ganzen gewählt, wo ich eig wirklich nur wenige Felder des Vorgängers und Nachfolgers benötige.

Ich habe damals anstatt dem Feld time ein Feld order gehabt, mit einer laufenden Nummer und konnte dann folgende Abfrage ausführen:

Code:

SELECT d.id, d.time, d1.id as privid, d2.id as nextid
FROM `data` as d

    LEFT OUTER JOIN `data` as d1
        ON d1.order = d.order - 1

    LEFT OUTER JOIN `data` as d2
        ON d2.order = d.order + 1

WHERE d.id = 4;

Ich hatte gehofft, dass eine solche Abfrage pro Datensatz möglich wäre, wobei in den beiden ON Teilen dann nach dem größten und dem kleinsten Datensatz, welcher kleiner bzw größer als der gefundene Datensatz ist?! ...oder hat meine Abfrage oben auch pro JOIN eine Art Sub-Select zur Folge, sodass diese Abfrage weniger optimiert ist als eine mit UNION oder richtigen Sub-Selects?

@thomas_w: Sind das dann aber nicht vier Sub-Selects pro Datensatz und daher schlechter optimiert als die zwei einzelnen Abfragen/Sub-Selects pro Datensatz?

28.04.2010, 09:44

Zitat:

Zitat von Sirke

@thomas_w: Sind das dann aber nicht vier Sub-Selects pro Datensatz und daher schlechter optimiert als die zwei einzelnen Abfragen/Sub-Selects pro Datensatz?

Welcher Query letztlich schneller ist, musst Du messen und per "EXPLAIN SELECT ..." prüfen, ob MySQL passende Indices findet, um die Abfrage performant auszuführen.

Für mein Beispiel (siehe #5) wären folgender Index sinnvoll:

Code:

CREATE INDEX sx_data_01 ON data (time, id);

Wenn die ID eindeutig lückenlos und fortlaufend ist, dann läßt sich der Vorgänger natürlich auch damit ermitteln.

Bei timestamp geht ein "-1" und "+1" nicht, deshalb ja die MIN(), MAX() Aggregationen, um den Vorgänger bzw. Nachfolger zu finden.

Die SUB-Query greifen direkt auf den Index zu, dass sollte schnell gehen,
aber bestimmt gibt es auch andere Lösungen für das Problem.

Grüße
Thomas

nikosch · 28.04.2010, 15:59

Von lückenlosen IDs auszugehen, davon kann man nur immer wieder abraten. Auch eine inkrementierende Hilfsvariable ist eher unrealisitisch auf lange Sicht zu pflegen.

Welchen Hintergrund hat denn die Anwendung? Was wird da verwaltet und dargestellt?

Sirke · 28.04.2010, 17:00

Zitat:

Zitat von nikosch

Von lückenlosen IDs auszugehen, davon kann man nur immer wieder abraten. Auch eine inkrementierende Hilfsvariable ist eher unrealisitisch auf lange Sicht zu pflegen.

Genau aus diesem Grund wollte ich von dem zusätzlichen Feld für eine laufende Nummer zum Sortieren und Suchen der Vorgänger und Nachfolger weg. Ich denke zwar, dass meine Abfrage oben mit dieser laufenden Nummer um einiges performanter ist als eine Abfrage mit Sub-Selects, aber den Mehraufwand für das korrekte Einfügen von Datensätzen bzw. fpr das Überprüfen von Fehlern lohnt es auf keinen Fall.

Ich verwende jetzt folgende Abfrage mit zwei Sub-Selects, welche wunderbar funktioniert und mit meiner (noch) recht kleinen Tabelle (zZt ~500 Einträge) noch gute Zeiten hinlegt:

Code:

SELECT d.id, (
    SELECT d1.id
    FROM `data` AS d1
    WHERE d1.time < d.time
      AND d1.group = d.group
    ORDER BY d1.time DESC
    LIMIT 1
) AS privid, (
    SELECT d2.id
    FROM `data` AS d2
    WHERE d2.time > d.time
      AND d2.group = d.group
    ORDER BY d2.time ASC
    LIMIT 1
) AS nextid
FROM `data` AS d

Zitat:

Zitat von nikosch

Welchen Hintergrund hat denn die Anwendung? Was wird da verwaltet und dargestellt?

Das sind gruppierte Datensätze, welche Text, Metadaten und ein paar Verknüpfungen enthalten. Bei der Darstellung einzelner Datensätze möchte ich Informationen zu den Vorgängern und Nachfolgern anzeigen lassen.

Noch eine kleine Frage zum Sub-Select, dann mache ich das Thema dicht, weil der Query soweit gut funktioniert, auch wenn die Tabellengröße evtl iwann das Thema wieder aktuell werden lassen könnte:
Kann ich von einem Sub-Select auch mehr als ein Wert zurückgeben lassen, so wie in etwa in einer solchen Abfrage (ähnlich wie bei dem Befehl list() von PHP):

Code:

SELECT d.id, (

    SELECT d1.id, d1.time              <<-- HIER
    FROM `data` AS d1
    WHERE d1.time < d.time
      AND d1.group = d.group
    ORDER BY d1.time DESC
    LIMIT 1

) AS ( privid, privtime)               <<-- HIER
FROM `data` AS d

nikosch · 28.04.2010, 17:11

Das verstehst Du falsch. Das AS bildet den ALias für die Subselectergebnistabelle, die Du im äußeren Select darüber ansprechen kannst. Die Feldangaben am ersten HIER werden nach außen durchgereicht, außer sie kollidieren mit Datenfeldern des äußeren Select, dann solltest Du auch dafür Aliase verwenden.

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
result Variablen von SQL verbinden	vci	PHP Tipps 2010	15	05.02.2010 19:09
SELECT WHERE datetime abfrage - Optimierung	mrSpok	Datenbanken	15	23.03.2006 19:08

27.04.2010, 23:38
Sirke Erfahrener Benutzer Registriert seit: 04.07.2003 Beiträge: 359 PHP-Kenntnisse: Fortgeschritten	Optimierung einer Abfrage eines Datensatzes samt Vorgänger und Nachfolger Hallo zusammen, Ich hoffe mal einen passenden Titel gefunden zu haben, auch wenn diese etwas lang geworden ist! Ich habe eine ähnliche Tabelle wie diese: Code: CREATE TABLE `data` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , `group` INT UNSIGNED NOT NULL , `time` TIMESTAMP NOT NULL ); Ähnlich heißt in diesem Fall, dass die Felder anders heißen und weitere Felder in der Tabelle existieren, welche jedoch für dieses Beispiel uninteressant sind. In dieser Tabelle speicher ich Datensätze in unterschiedlichen Gruppen mit dem Timestamp des Zeitpunktes des Erstellens ab, welcher als Sortierkriterium dient! Nun möchte ich einen bestimmten Datensatz zusammen mit dem zeitlichen Vorgänger und dem Nachfolger in möglichst einer Abfrage erhalten. Früher hatte ich ein ähnliches Problem schon einmal, jedoch war das Sortierkriterium dort eine vorlaufende Nummer, sodass der Vorgänger durch +1 und der Nachfolger durch -1 ausgewählt werden konnte. In diesem Fall ist der Zeitpunkt des Vorgängers und des Nachfolgers jedoch nicht bekannt, sodass ich nicht weiß, wie ich diese Abfrage durchführen kann. Habe ich nun zum Beispiel folgende Datensätze in der DB Code: INSERT INTO `data` (`id`, `group`, `time`) VALUES (1, 1, '2010-01-01 01:23:00'), (2, 1, '2010-02-01 04:56:00'), (3, 1, '2010-03-01 07:23:00'), (4, 1, '2010-04-01 09:47:00'), (5, 1, '2010-04-03 02:01:00'), (6, 1, '2010-04-05 03:39:00'); und möchte einen Datensatz samt Nachfolger und Vorgänger ermitteln, führe ich zur Zeit drei Abfragen nacheinander aus: Code: SELECT * FROM data WHERE id=4; SELECT * FROM data WHERE time < $time AND group = $group time DESC LIMIT 1; SELECT * FROM data WHERE time > $time AND group = $group time ASC LIMIT 1; wobei $time und $group von dem Datensatz der ersten Abfrage stammen! Sobald ich nun eine Liste von N Datensätzen eine Gruppe abfragen möchte, benötige ich 2*N + 1 Abfragen für Vorgänger und Nachfolger, was etwas viel ist, wenn ich das womöglich in einer Abfrage erhalten könnte! Gibt es eine Möglichkeit dies mit einer Abfrage durchzuführen oder benötige ich dafür zwingend ein Sortierkriterium, wo das des Vorgängers und das Nachfolgers bekannt ist? Vielen Dank und ich hoffe meine Frage/mein Problem ist verständlich? Grüße, Sirke


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

28.04.2010, 09:12
Sirke Erfahrener Benutzer Registriert seit: 04.07.2003 Beiträge: 359 PHP-Kenntnisse: Fortgeschritten	Ich weiß, dass UNION und Sub-Selects möglich sind, aber führt das immer zu einem neuen Select und damit doch auch zu einer neuen (internen) Abfrage, oder? @ChrisB: Okay, das mit dem * war eig nur für eine kurze Schreibsweise des ganzen gewählt, wo ich eig wirklich nur wenige Felder des Vorgängers und Nachfolgers benötige. Ich habe damals anstatt dem Feld time ein Feld order gehabt, mit einer laufenden Nummer und konnte dann folgende Abfrage ausführen: Code: SELECT d.id, d.time, d1.id as privid, d2.id as nextid FROM `data` as d LEFT OUTER JOIN `data` as d1 ON d1.order = d.order - 1 LEFT OUTER JOIN `data` as d2 ON d2.order = d.order + 1 WHERE d.id = 4; Ich hatte gehofft, dass eine solche Abfrage pro Datensatz möglich wäre, wobei in den beiden ON Teilen dann nach dem größten und dem kleinsten Datensatz, welcher kleiner bzw größer als der gefundene Datensatz ist?! ...oder hat meine Abfrage oben auch pro JOIN eine Art Sub-Select zur Folge, sodass diese Abfrage weniger optimiert ist als eine mit UNION oder richtigen Sub-Selects? @thomas_w: Sind das dann aber nicht vier Sub-Selects pro Datensatz und daher schlechter optimiert als die zwei einzelnen Abfragen/Sub-Selects pro Datensatz?

28.04.2010, 15:59
nikosch moderatives Dielektrikum Registriert seit: 21.05.2008 Beiträge: 35.987 PHP-Kenntnisse: Fortgeschritten	Von lückenlosen IDs auszugehen, davon kann man nur immer wieder abraten. Auch eine inkrementierende Hilfsvariable ist eher unrealisitisch auf lange Sicht zu pflegen. Welchen Hintergrund hat denn die Anwendung? Was wird da verwaltet und dargestellt? __________________ -- One pixel is still too big. Please make it smaller. ASAP. Initiative Mittelstand. Die wichtigste Gestaltungsregel im Screendesign ist Pi mal Daumen des Arbeitgebers. --

28.04.2010, 17:11
nikosch moderatives Dielektrikum Registriert seit: 21.05.2008 Beiträge: 35.987 PHP-Kenntnisse: Fortgeschritten	Das verstehst Du falsch. Das AS bildet den ALias für die Subselectergebnistabelle, die Du im äußeren Select darüber ansprechen kannst. Die Feldangaben am ersten HIER werden nach außen durchgereicht, außer sie kollidieren mit Datenfeldern des äußeren Select, dann solltest Du auch dafür Aliase verwenden. __________________ -- One pixel is still too big. Please make it smaller. ASAP. Initiative Mittelstand. Die wichtigste Gestaltungsregel im Screendesign ist Pi mal Daumen des Arbeitgebers. --