Ankündigung

Einklappen
Keine Ankündigung bisher.

Optimierung einer Abfrage eines Datensatzes samt Vorgänger und Nachfolger

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • 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


  • #2
    Zitat von Sirke Beitrag anzeigen
    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.)

    Kommentar


    • #3
      Zitat von Sirke Beitrag anzeigen
      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

      Kommentar


      • #4
        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?

        Kommentar


        • #5
          Zitat von Sirke Beitrag anzeigen
          @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

          Kommentar


          • #6
            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?
            --

            „Emoticons machen einen Beitrag etwas freundlicher. Deine wirken zwar fachlich richtig sein, aber meist ziemlich uninteressant.
            Wenn man nur Text sieht, haben viele junge Entwickler keine interesse, diese stumpfen Texte zu lesen.“


            --

            Kommentar


            • #7
              Zitat von nikosch Beitrag anzeigen
              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 von nikosch Beitrag anzeigen
              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

              Kommentar


              • #8
                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.
                --

                „Emoticons machen einen Beitrag etwas freundlicher. Deine wirken zwar fachlich richtig sein, aber meist ziemlich uninteressant.
                Wenn man nur Text sieht, haben viele junge Entwickler keine interesse, diese stumpfen Texte zu lesen.“


                --

                Kommentar

                Lädt...
                X