MySQL: Datensazu vor und nach Datensatz X ermitteln

[AllesMeins]

Hiho,

vielleicht sehe ich (mal wieder) den Wald vor lauter Bäumen nicht. Ich habe in der Tabelle mehrer Datensätze, die ich über 3 ORDER BY Kriterien in die gewünschte Reihenfolge bringen kann. Nun mchte ich gerne einen bestimmten datensatz rauspicken und dazu die beiden Datensätze da "drumherum" also den der nach den ORDER BY Kritereien einen davor liegt und der, der danach folgt.

Habt ihr irgendwelche denkanstösse oder Lösungen?

Grüsse

Marc

[Ludger]

Hi,

vielleicht sehe ich (mal wieder) den Wald vor lauter Bäumen nicht. Ich habe in der Tabelle mehrer Datensätze, die ich über 3 ORDER BY Kriterien in die gewünschte Reihenfolge bringen kann. Nun mchte ich gerne einen bestimmten datensatz rauspicken und dazu die beiden Datensätze da "drumherum" also den der nach den ORDER BY Kritereien einen davor liegt und der, der danach folgt.

wenn Du auf die genannte Tabelle ein SQL-Statement loslaesst (und das tust Du ja auch), dann gibts eine Datensatzmenge (eine Matrix) zurueck. Diese bearbeitest Du dann wohl mit der auf dem Webserver laufenden Logik (PHP?) und pickst Dir einen Datensatz heraus, korrekt?

Nun, ich wuerde sowas jedenfalls mit bspw. PHP machen und SQL nicht bemuehen. Falls es ums Herauspicken (und anderen Pickereien) aus einer sortierten Datensatzmenge geht.

Gruss,
Ludger

[wahsaga]

Also Ludger,

rühmst du dich nicht immer, wenigstens zum thema datenbanken hier auch ab und an mal was fachlich sinnvolles beizutragen zu haben ...?

wenn Du auf die genannte Tabelle ein SQL-Statement loslaesst (und das tust Du ja auch), dann gibts eine Datensatzmenge (eine Matrix) zurueck. Diese bearbeitest Du dann wohl mit der auf dem Webserver laufenden Logik (PHP?) und pickst Dir einen Datensatz heraus, korrekt?

Nun, ich wuerde sowas jedenfalls mit bspw. PHP machen und SQL nicht bemuehen. Falls es ums Herauspicken (und anderen Pickereien) aus einer sortierten Datensatzmenge geht.

wenn man lediglich zu datensatz x den davor und den danach haben möchte, ist es unperformater unfug, den _kompletten_ datenbestand vom mysql- an den webserver zu übertragen, nur um dort die selektion vorzunehmen.

gängigste lösung für das problem ist die aufteilung auf drei queries (ja, auch nicht unbedingt hochperformant, aber immer noch besser als alle datensätze auszulesen, wenn der großteil überhaupt nicht benötigt wird).

eine query holt den eigentlichen datensatz, und die anderen beiden jeweils den "kleinsten unter" bzw. "größten über" diesem datensatz - größer/kleiner natürlich in bezug auf die gewählte sortierordnung. LIMIT hilft, dabei immer nur einen der datensätze zu bekommen.

beispiel mit nummerischer ID als sortierkriterium, gewünscht sei datensatz mit ID 15 und der davor/dahinter:

SELECT [spalten] FROM tabelle WHERE id = 15
SELECT [spalten] FROM tabelle WHERE id < 15 ORDER BY id DESC LIMIT 1
SELECT [spalten] FROM tabelle WHERE id > 15 ORDER BY id ASC LIMIT 1

und wenn man von den inhalten des vorherigen/folgenden datensatz ausschließlich die IDs braucht (bspw. zur realisierung einer "blätter-funktion" o.ä.), kann man in den letzten beiden queries auch nur die spalte id selektieren, während man im ersten select ja vermutlich an mehr spalteninhalten interessiert ist.

gruß,
wahsaga

[Ludger]

Hi,

rühmst du dich nicht immer, wenigstens zum thema datenbanken hier auch ab und an mal was fachlich sinnvolles beizutragen zu haben ...?

Du alter Heizforsch!   :-)

wenn man lediglich zu datensatz x den davor und den danach haben möchte, ist es unperformater unfug, den _kompletten_ datenbestand vom mysql- an den webserver zu übertragen, nur um dort die selektion vorzunehmen.

Der Datenbestand ist doch schon bei der serverseitig arbeitenden Logik angekommen. Es geht also nur noch ums Picken, und dieses soll wahlfrei geschehen.

Gruss,
Ludger

[AllesMeins]

Hiho,

Der Datenbestand ist doch schon bei der serverseitig arbeitenden Logik angekommen. Es geht also nur noch ums Picken, und dieses soll wahlfrei geschehen.

Nein, die daten liegen erst mal in der MySQL DB (diese ist zwar oft auf dem selben Server, es gibt aber auch durchaus Architekturen mit einzelnen Datenbankservern) und von dort müssten dann alle datensätze zu PHP geschaufelt werden. Das kostet zum einen einen ganzen haufen Speicher, weil alles in PHP vorgehalten werden muss und auch recht viel zeit - selbst wenn Datenbank und Webserver sich einen rechner teilen.

Marc

[Ludger]

Hi,

Nein, die daten liegen erst mal in der MySQL DB (diese ist zwar oft auf dem selben Server, es gibt aber auch durchaus Architekturen mit einzelnen Datenbankservern) und von dort müssten dann alle datensätze zu PHP geschaufelt werden. Das kostet zum einen einen ganzen haufen Speicher, weil alles in PHP vorgehalten werden muss und auch recht viel zeit - selbst wenn Datenbank und Webserver sich einen rechner teilen.

dann hatte ich Dich falsch verstanden.

Bedenke allerdings, dass - wenn Du mehrfachst "pickst" - die "Kosten" fuers Ausfuehren der SQL-Statements hoeher sein koennen als die Einsparungen, die Du erzielst durch das Nichtholen des gesamten zu bearbeitenden Datenbestands.

Gruss,
Ludger

[AllesMeins]

Hiho,

Bedenke allerdings, dass - wenn Du mehrfachst "pickst" - die "Kosten" fuers Ausfuehren der SQL-Statements hoeher sein koennen als die Einsparungen, die Du erzielst durch das Nichtholen des gesamten zu bearbeitenden Datenbestands.

naja, ich hab ja ein gesundes Selbstvertrauen, aber soweit das ich erwarte mit PHP einen grossen Datensatz so viel besser durchsuchen zu können, das ich trotz kompletten transfer aller Daten immer noch besser bin als das darauf spezialisierte MySQL, reicht es dann doch nicht :)

Marc

[Ilja]

yo,

rühmst du dich nicht immer, wenigstens zum thema datenbanken hier auch ab und an mal was fachlich sinnvolles beizutragen zu haben ...?

ich denke mal, wir alle haben unsere stärken und schwächen, deine lösung ist meiner meinung nach auch nicht das gelbe vom ei. es zeigst sich doch immer wieder, dass wir alle "nur" menschen sind....

SELECT [spalten] FROM tabelle WHERE id = 15
SELECT [spalten] FROM tabelle WHERE id < 15 ORDER BY id DESC LIMIT 1
SELECT [spalten] FROM tabelle WHERE id > 15 ORDER BY id ASC LIMIT 1

das problem daran ist, dass du datensätze über WHERE klausel über die ID ausschließt, die sehr wohl aber wichtig in der sortierung sein können. und da er über drei spalten sortiert, wird die id wenn überhaupt nur eines der drei kriterien sein und ich denke mal eher gar nicht vorkommen. insofern kann eine ID = 36 sehr wohl laut seiner sortierung vor dem datensatz id = 15 auftreten, du schließt ihn aber aus. ergo geht deine lösung nicht.

Ilja

[Ludger]

Hi,

rühmst du dich nicht immer, wenigstens zum thema datenbanken hier auch ab und an mal was fachlich sinnvolles beizutragen zu haben ...?

ich denke mal, wir alle haben unsere stärken und schwächen, deine lösung ist meiner meinung nach auch nicht das gelbe vom ei. es zeigst sich doch immer wieder, dass wir alle "nur" menschen sind....

Du solltest auch besser nicht mehr den Papst mobben ...

das problem daran ist, dass du datensätze über WHERE klausel über die ID ausschließt, die sehr wohl aber wichtig in der sortierung sein können. und da er über drei spalten sortiert, wird die id wenn überhaupt nur eines der drei kriterien sein und ich denke mal eher gar nicht vorkommen. insofern kann eine ID = 36 sehr wohl laut seiner sortierung vor dem datensatz id = 15 auftreten, du schließt ihn aber aus. ergo geht deine lösung nicht.

Das war ein Beispiel, sonst nichts. Bisschen mehr abstrahieren, bitte.   :-)

Gruss,
Ludger

[dedlfix]

echo $begrüßung;

das problem daran ist, dass du datensätze über WHERE klausel über die ID ausschließt, die sehr wohl aber wichtig in der sortierung sein können. [...] ergo geht deine lösung nicht.

HAVING (zwischen ORDER und LIMIT) sollte gehen. MySQL pocht auch nicht drauf, HAVING nur zusammen mit Aggregatfunktionen auszuführen.

echo "$verabschiedung $name";

[Ilja]

yo,

HAVING (zwischen ORDER und LIMIT) sollte gehen. MySQL pocht auch nicht drauf, HAVING nur zusammen mit Aggregatfunktionen auszuführen.

GROUP BY und HAVING waren auch meine ersten gedanken, aber ich bin noch nicht auf eine lösung gekommen. es wäre interessant zu wissen, ob er die abfrage über php macht oder ob er variablen einsetzen kann.

Ilja

[wahsaga]

hi,

das problem daran ist, dass du datensätze über WHERE klausel über die ID ausschließt, die sehr wohl aber wichtig in der sortierung sein können. und da er über drei spalten sortiert

ja, in dem fall wird es nicht so einfach.

in vielen fällen sollte es aber möglich sein, die drei sortierkriterien zu einer "pseudo-spalte" zusammenzufügen - CONCAT o.ä. - und dann diese kombination für den größer-kleiner-vergleich herzunehmen.
klar, dabei muss man mit alphanummerischer/nummerischer sortierung ein bisschen aufpassen, ebenso darauf, dass man spalteninhalte ungleicher länge alle auf die gleiche länge "aufbohrt", um korrekt sortieren zu können.
für generell unmöglich auf diesem wege zu lösen würde ich ein derartiges problem jedenfalls nicht halten.

klar, ob eine WHERE-abfrage auf eine solche zusammengesetzte spalte sonderlich performant ist, wäre auch zumindest diskussionswürdig. besser als die erst mal alles rüberschaufeln-lösung dürfte es aber oftmals noch sein.

und je nach datenkonstellation und abfrage/update-häufigkeit könnte man auch noch überlegen, dieses verbundene sortierkriterium in einer extra spalte abzulegen (und diese ggf. noch zu indizieren), um den zugriff darauf weniger teuer zu gestalten - in abwägung des dadurch entstehenden erhöhten speicheraufwandes und der redundanz sowie des etwas erhöhten verwaltungsaufwandes (aktualisieren dieser zusätzlichen spalte bie insert/update).

im allgemeinen kann man wohl nur sagen, "es kommt drauf an" - und zwar auf die konkrete situation, die wir nicht kennen.

gruß,
wahsaga

[Tom]

Hello,

yo,

rühmst du dich nicht immer, wenigstens zum thema datenbanken hier auch ab und an mal was fachlich sinnvolles beizutragen zu haben ...?

ich denke mal, wir alle haben unsere stärken und schwächen, deine lösung ist meiner meinung nach auch nicht das gelbe vom ei. es zeigst sich doch immer wieder, dass wir alle "nur" menschen sind....

Lock
  SELECT [spalten] FROM tabelle WHERE [match_cat] = $suchbegriff
  SELECT [spalten] FROM tabelle WHERE [match_cat] < $suchbegriff ORDER BY [match_cat_idx] DESC LIMIT 1
  SELECT [spalten] FROM tabelle WHERE [match_cat] > $suchbegriff ORDER BY [match_cat_idx] ASC LIMIT 1
unlock

Das ist zwar nicht ganz billig, aber er wird zwangsweise über die Suchbegriffe einen eigenen Index aufmachen müssen. Ob MySQL allerdings merkt, dass es diesen Index über die drei Spalten auch in der ersten Abfrage benutzen darf, weiß ich noch nicht.

[match_cat] = [spalte1] + [spalte2] + [spalte3]

[match_cat_idx] ist der Index über diese Contatination

Die Bindung durch Lock ist unbedingt erforderlich, solange es kein Common oder Union gibt.

Harzliche Grüße vom Berg
esst mehr http://www.harte-harzer.de

Tom

[AllesMeins]

Hiho,

ich habe aber eigentlich kein Interesse daran die komplette Tabelle auslesen zu müssen und dann alle datensätze nochmal mit PHP zu durchwühlen, wenn ich eigentlich nur 3 Stück brauche. Das ist doch arg unoptimal.

Marc

[Ilja]

yo,

ich habe mir ein wenig den kopf über die abfrage zerbrochen, ohne  zu merken, dass ich den falschen ansatz habe. der clou ist, dass man gerne immer die ID des bestimmten datensatzes zu rate ziehen will. das ist aber der falsche weg, da es um sortierungen geht. deshalb muss man in der ersten abfrage, die drei sortierungs-spalten der bestimmten ID ermitteln und dann mit diesen weiterarbeiten. und weil wir später mit aggregat-funktionen arbeiten, müssen wir die drei spalten auch noch zusammenführen

SELECT Spalte1 || Spalte2 || Spalte3
FROM Tabelle
WHERE ID = $wert

das ganze nehmen wir als unterabfrage und kombinieren es mit UNION

SELECT Spalte1, Spalte2, Spalte 3, MAX(Spalte1 || Spalte2 || Spalte3) AS Test
FROM tabelle
GROUP BY Spalte1, Spalte2, Spalte 3
WHERE Test <
   (
   SELECT Spalte1 || Spalte2 || Spalte3
   FROM Tabelle OR
   WHERE ID = $wert
   )

UNION

SELECT Spalte1, Spalte2, Spalte 3, MIN(Spalte1 || Spalte2 || Spalte3) AS Test
FROM tabelle
GROUP BY Spalte1, Spalte2, Spalte 3
WHERE Test >
   (
   SELECT Spalte1 || Spalte2 || Spalte3
   FROM Tabelle OR
   WHERE ID = $wert
   )

und wenn ich mir nun das ganze anschaue, dann habe ich wenig vertrauen, dass du damit die beiden gewünschten datensätze bekommst, geschweige denn, dass die syntax richtig ist. ich glaube, da habe ich ziemlichen mist geschrieben....

Ilja

[Ilja]

yo,

wie ich dachte, das war nichts mit dem ersten versuch, da hatte ich ein paar falsche gedanken. einfach ignorieren die abfrage...

ilja

[wahsaga]

hi,

vielleicht sehe ich (mal wieder) den Wald vor lauter Bäumen nicht. Ich habe in der Tabelle mehrer Datensätze, die ich über 3 ORDER BY Kriterien in die gewünschte Reihenfolge bringen kann.

nachdem sich diese sortierung nach drei kriterien jetzt als der haken an der sache herausgestellt hat, muss ich noch mal nachfragen:

Nun mchte ich gerne einen bestimmten datensatz rauspicken und dazu die beiden Datensätze da "drumherum" also den der nach den ORDER BY Kritereien einen davor liegt und der, der danach folgt.

auf welche weise erfolgt denn dieses herauspicken überhaupt?
dass die ID dafür bei einer sortierung nach drei kriterien eher untauglich ist, hatten wir ja schon.

also was dann? position in der vorliegenden sortierung? wenn die zur auswahl von datensatz x dient, dann sind der davor und dahinter liegende über ein angepasstes LIMIT ja leicht zu ermitteln.

probleme kriegst du damit wiederum aber dann, wenn ein neuer datensatz eingefügt wird - und damit die "sortierung" durcheinander bringt.

gruß,
wahsaga

[Ilja]

yo,

nachdem mir meine freundin erst den kopf gewaschen und dann die haare geschnitten hat, hatte ich ein wenig zeit über die abfrage nachzudenken. mein erste ansatz war schon der richtige, das ziel erreicht man índem man die spaltensortierung mitnimmt. aber meine erste umsetzung war nicht so ganz das gelbe vom ei.

die unterabfrage bleibt, wir brauchen sie, um die 3 spalten zu "merken". nur teilen wir sie diesmal auf, damit wir sie in die WHERE klausel einbauen können, sortieren sie und schneiden jeweils den ersten datensatz ab.

SELECT t1.id, t1.Spalte1, t1.Spalte2, t1.Spalte3
FROM tabelle AS t1
WHERE t1.spalte1 <=
  (SELECT u1.Spalte1 FROM Tabelle AS u1 WHERE u1.ID = $wert)
AND t1.spalte2 <=
  (SELECT u2.Spalte2 FROM Tabelle AS u2 WHERE u2.ID = $wert)
AND t1.spalte3 <=
  (SELECT u3.Spalte3 FROM Tabelle AS u3 WHERE u3.ID = $wert)
ORDER BY t1.spalte1 DESC ,t1.spalte2 DESC,t1.spalte3 DESC
LIMIT 1

UNION

SELECT t1.id, t1.Spalte1, t1.Spalte2, t1.Spalte3
FROM tabelle AS t1
WHERE t1.spalte1 >=
  (SELECT u1.Spalte1 FROM Tabelle AS u1 WHERE u1.ID = $wert)
AND t1.spalte2 >=
  (SELECT u2.Spalte2 FROM Tabelle AS u2 WHERE u2.ID = $wert)
AND t1.spalte3 >=
  (SELECT u3.Spalte3 FROM Tabelle AS u3 WHERE u3.ID = $wert)
ORDER BY t1.spalte1,t1.spalte2,t1.spalte3
LIMIT 1

das sieht doch schon viel besser aus und klappt auch hoffentlich in der praxis...

Ilja

[Ilja]

yo,

wirklich nicht mein tag heute, die WHERE KLausel mit den unterabfragen stimmt noch nicht ganz, sie schließt zuviele datensätze aus.

SELECT t1.id, t1.Spalte1, t1.Spalte2, t1.Spalte3
FROM tabelle AS t1
WHERE t1.spalte1 <
  (SELECT u1.Spalte1 FROM Tabelle AS u1 WHERE u1.ID = $wert)
OR t1.spalte1 =
  (SELECT u2.Spalte1 FROM Tabelle AS u2 WHERE u2.ID = $wert)
AND t1.spalte2 <
  (SELECT u3.Spalte2 FROM Tabelle AS u3 WHERE u3.ID = $wert)
OR t1.spalte1 =
  (SELECT u4.Spalte1 FROM Tabelle AS u4 WHERE u4.ID = $wert)
AND t1.spalte2 =
  (SELECT u5.Spalte2 FROM Tabelle AS u5 WHERE u5.ID = $wert)
AND t1.spalte3 <=
  (SELECT u6.Spalte3 FROM Tabelle AS u6 WHERE u6.ID = $wert)
ORDER BY t1.spalte1 DESC ,t1.spalte2 DESC,t1.spalte3 DESC
LIMIT 1

UNION

SELECT t1.id, t1.Spalte1, t1.Spalte2, t1.Spalte3
FROM tabelle AS t1
WHERE t1.spalte1 >
  (SELECT u1.Spalte1 FROM Tabelle AS u1 WHERE u1.ID = $wert)
OR t1.spalte1 =
  (SELECT u2.Spalte1 FROM Tabelle AS u2 WHERE u2.ID = $wert)
AND t1.spalte2 >
  (SELECT u3.Spalte2 FROM Tabelle AS u3 WHERE u3.ID = $wert)
OR t1.spalte1 =
  (SELECT u4.Spalte1 FROM Tabelle AS u4 WHERE u4.ID = $wert)
AND t1.spalte2 =
  (SELECT u5.Spalte2 FROM Tabelle AS u5 WHERE u5.ID = $wert)
AND t1.spalte3 >=
  (SELECT u6.Spalte3 FROM Tabelle AS u6 WHERE u6.ID = $wert)
ORDER BY t1.spalte1, t1.spalte2,t1.spalte3
LIMIT 1

und das sind dann doch nicht mehr so gut aus, naja, der wille war da, das fleisch ist heute aber irgendwie schwach. daür kann ich meinem lieblingssatz auch bei mir anwenden, wir sind alle nur menschen, auch ich....

Ilja