Vorherige und nachfolgende ID ermitteln

Ein relativ häufig benötigtes Feature bei Webanwendungen sind Vor- und Zurück-Links. Ich meine aber nicht die Buttons vom Browser sondern wenn man beispielsweise eine beliebige Nachricht in einem Postfach aufgerufen hat, dann gibt es einen Link zur vor dieser Mail eingetroffenen Nachricht und einen für die darauffolgende. Das ist nicht nur bei einem Posteingang so, sondern man kann das überall wiederfinden: bei Online-Shops, Blogs, Foren – überall, wo ein zeitlicher (oder zumindest sequentieller) Bezug zwischen den einzelnen Seiten besteht. In diesem Beitrag soll es nun darum gehen, wie man die ID dieser beiden Datensätze (Vorgänger und Nachfolger) am effektivsten mit SQL selektieren kann.

Wir gehen davon aus, dass die ID eine AUTO_INCREMENT-Spalte vom Typ INT ist – wir können also mit Mathematik arbeiten.

Eines vorweg: Wir können natürlich nicht davon ausgehen, dass der Vorgänger einfach AKTUELLE_ID – 1 ist, das wär dann doch ein wenig zu einfach. Es werden ja immer mal wieder Datensätze gelöscht und so entstehen Lücken. Diese zu korrigieren (bei Löschen eines Datensatzes alle größeren IDs dekrementieren) ist zwar möglich aber recht aufwändig und unnötig. Wenn man es aber macht, erspart man sich natürlich sämtliche Überlegungen, die im Folgenden angestellt werden, da dann tatsächlich einfach -1 für den Vorgänger und +1 für den Nachfolger gerechnet werden müsste. Man bräuchte also die Datenbank gar nicht zu befragen.

Wir gehen jetzt aber von einer lückenhaften Datenbank aus. Damit ist gemeint, dass nicht alle Wertmöglichkeiten zwischen 1 und MAX(ID) belegt sind. Wenn wir also eine ID x haben, kann der Vorgänger mitunter x-1 sein, bei anderem x aber vielleicht auch x-3 oder noch weiter entfernt.

Nun kommen wir mal zur Praxis. Für diese Aufgabe gibt es wie immer mehrere Lösungsansätze. Diese sollen im Folgenden vorgestellt werden. Es soll erstmal nur für die Ermittlung des Vorgängers eine Abfrage gefunden werden. Die für den Nachfolger wird recht ähnlich.

 SELECT ID   
 FROM tabelle   
 WHERE ID<".$aktuelle_ID."  
 ORDER BY ID DESC   
 LIMIT 1

Dieser Ansatz sucht erstmal alle Datensätze, die eine kleinere ID als der aktuelle Datensatz haben und sortiert anschließend. Der größte Wert wird dann zurückgegeben. Ich erläutere weiter unten, warum dieser Weg der effektivste ist.

Ein zweiter mir per Mail vorgeschlagener Weg ist dieser:

SELECT ID  
FROM tabelle  
WHERE ID-".$aktuelle_ID."<0 
ORDER BY ID-".$aktuelle_ID."
ASC  LIMIT 1

Bei dieser Variante werden ebenfalls zuerst alle Datensätze mit kleinerer ID selektiert, deisemal allerdings über die Pürfung, ob die Differenz zwischen ID und aktueller ID kleiner 0 ist. Von diesen muss dann der Wert gewählt werden, der den geringsten Abstand zur aktuellen ID hat.
Einfach die WHERE-Bedingung weglassen funktioniert übrigens nicht, auch nicht, wenn man nach ABS(ID-„.$aktuelle_ID.“) sortiert, da dann nicht zwingend ein Vorgänger herauskommen muss. Und wenn man LIMIT 2 anhängt hat man auch nicht zwingend einen Vorgänger und einen Nachfolger, sondern es kann vorkommen, dass man dann 2 Vorgänger oder 2 Nachfolger bekommt.

Warum nun ist die erste Variante die bessere?
Ganz einfach: Weil Sie kein Filesort benötigt, sondern den PK bzw. den zugehörigen Index nutzen kann. Das ist so, weil für Funktionen bzw. Berechnungen kein Index möglich ist.
Außerdem kann die zweite Variante nicht verwendet werden, wenn man die Spalte ID als UNSIGNED INT definiert hat. Ich war eben selbst ein wenig erstaunt, aber wenn man da in den negativen Bereich kommt schlägt er einfach um und subtrahiert den Rest von MAX_INT.

Aus Performancesicht gibt es dementsprechend riesige Unterschiede, weil für die erste Variante der Index sowohl für die WHERE-Bedingung als auch für das Sortieren benutzt werden kann.
Die erste Variante benötigt 0,0003 Sekunden, während die zweite Variante knapp unter einer Sekunde bleibt.

Wenn man nun Vorgänger und Nachfolger selektieren möchte, muss man das nicht mit 2 Abfragen machen, sondern kann die selektierten Mengen (Vorgänger-Menge und Nachfolger-Menge) durch UNION vereinigen.

(SELECT ID   
FROM tabelle   
WHERE ID<".$aktuelle_ID."
ORDER BY ID DESC   
LIMIT 1) UNION (  
SELECT ID   
FROM tabelle   
WHERE ID>".aktuelle_ID."  
ORDER BY ID ASC  LIMIT 1  )

Das dauert bei mir jetzt 0,06 Sekunden, deshalb ist es durchaus auch überlegenswert es mit 2 einzelnen Abfragen zu versuchen.

Also ich hoffe, dass ich die effizientere Variante aufzeigen konnte, als die die mir per Mail vorgestellt wurde. Also weiß ich, dass mindestens eine Person nun seine Scripte ändern wird ????
Alle anderen, die eine solche Funktion benötigen, wissen nun auch, wie es am performantesten geht.