MySQL große UNION Verkettung und Performance

Diese Seite verwendet Cookies. Durch die Nutzung unserer Seite erklären Sie sich damit einverstanden, dass wir Cookies setzen. Weitere Informationen

  • MySQL große UNION Verkettung und Performance

    Hallo,

    ich muss sagen, ich bin nicht der SQL Held. Aber ich brauche Eure Hilfe. Denn die folgende Abfrage benötigt ca. eine halbe Sekunde - ZU LANGE!

    Das mag zum einen an meinem "Trabbi"-Server liegen, zum anderen sicher aber auch an dieser UNION-Geschichte. Die Abfrage sieht so aus:

    Quellcode

    1. (SELECT field1,field2,field3,field4,field5,field6,field7,field8,field9,field10 FROM table WHERE (`field7` <= '2011-09-15 01:54:00' AND `field7` > '2011-09-14 13:13:00' AND (TIMEDIFF(field7, field8) < '12:00:00')) AND field9=1 AND field10 LIKE 'aaa%' ORDER BY score DESC LIMIT 5)
    2. UNION
    3. ... für field9 = 1 von field10 like 'aaa%' bis 'ggg%' (also 7 mal)...
    4. UNION
    5. (SELECT field1,field2,field3,field4,field5,field6,field7,field8,field9,field10 FROM table WHERE (`field7` <= '2011-09-15 01:54:00' AND `field7` > '2011-09-14 13:13:00' AND (TIMEDIFF(field7, field8) < '12:00:00')) AND field9=1 AND field10 LIKE 'hhh%' ORDER BY score DESC LIMIT 2)
    6. UNION
    7. ... diesen ganzen block NOCHMAL mit field9 = 2,3,4,...,11 wiederholt ...
    8. UNION
    9. (SELECT field1,field2,field3,field4,field5,field6,field7,field8,field9,field10 FROM table WHERE (`field7` <= '2011-09-15 01:54:00' AND `field7` > '2011-09-14 13:13:00' AND (TIMEDIFF(field7, field8) < '12:00:00')) AND field9=11 AND field10 LIKE 'aaa%' ORDER BY score DESC LIMIT 4)
    10. UNION
    11. ... für field9 = 2,...,11 von field10 like 'aaa%' bis 'ggg%'...
    12. UNION
    13. (SELECT field1,field2,field3,field4,field5,field6,field7,field8,field9,field10 FROM table WHERE (`field7` <= '2011-09-15 01:54:00' AND `field7` > '2011-09-14 13:13:00' AND (TIMEDIFF(field7, field8) < '12:00:00')) AND field9=11 AND field10 LIKE 'hhh%' ORDER BY score DESC LIMIT 1)
    14. ORDER BY `field3` DESC //und dann noch sortiert
    Alles anzeigen


    Eindeutig ist diese Riesenabfrage Grund für die schlechte Performance. Wie kann ich das besser machen? Warum ich das so mache?

    Meine Datensätze haben ein Feld, das anzeigt, zu welcher Quelle sie gehören (field9). Das andere zeigt an, zu welcher Kategorie sie gehören (field10).
    field3 ist eine punkteskala (schönheit).

    es ist bereits berechnet, wie viele Datensätze pro Quelle/Kategorie-Kombination ausgegeben werden sollen.
    Ich will also mir für alle field9 und alle field10 eine vorbestimmte Anzahl von Datensätze ausgeben lassen

    Deshalb kommt für jedes SELECT ein anderes LIMIT zustande. Deshalb kette ich die SELECTS mit UNIONS aneinander. Macht das Sinn? Wahrscheinlich nicht!


    Wie geht es anders/besser?


    Grüße und Dank,

    EHW
  • Schneller wird es, wenn du statt >= und <= einfach BETWEEN nutzt. Dazu muss nur noch einmal gefiltert werden.
    Ansonsten: Wie sehen deine Indizes aus? Für deine Anfrage müsstest du wohl EINEN (!!) Index über field7,field9, field10,field11 und score haben. Das wird dich wesentlich schneller machen.
    Letztendlich ist auch die Reihenfolge der Index Spalten entscheidend. Die solltest du nach Sparsity der einzelnen Spalten festlegen.

    Lg
  • Torben Brodt schrieb:

    Schneller wird es, wenn du statt >= und <= einfach BETWEEN nutzt. Dazu muss nur noch einmal gefiltert werden.


    Alles klar, hab' ich geändert. Hat aber nicht so sehr viel rausgeholt.

    Torben Brodt schrieb:

    Für deine Anfrage müsstest du wohl EINEN (!!) Index über field7,field9, field10,field11 und score haben.


    Ich bin nicht sicher ob ich dich richtig verstehe. Index ist field1 für die Tabelle. field9 und field10 sind Indizes anderes Tabellen.
    Wenn ich bspw. Quellen 1,2,3 habe und Kategorien #aaa, #bbb, #ccc. Dann frage ich zu jeder der 9 Kombinationsmöglichkeiten z.B. 4 Datensätze der Kombination 1/#aaa, und 5 Datensätze 3/#bbb - Wie mache ich das mit EINEM (!!) Index?

    Torben Brodt schrieb:

    Reihenfolge der Index Spalten entscheidend. Die solltest du nach Sparsity der einzelnen Spalten festlegen.


    Welche Reihenfolge meinst Du diesbezüglich? Soll eine Spalte mir potenziell häufiger auftretenden null-Einträgen "weiter links stehen" als die andere?


    Noch eine Frage: Kann ich nicht im Vorfeld erstmal alle Datensätze, für die NICHT gilt

    Quellcode

    1. `field7` <= '2011-09-15 01:54:00' AND `field7` > '2011-09-14 13:13:00'


    ausschließe und meine Abfrage dann nur noch in der verbleibenden Menge an Datensätzen durchführe.


    Grüße,

    EHW
  • einheitswurzel schrieb:

    Ich bin nicht sicher ob ich dich richtig verstehe. Index ist field1 für die Tabelle. field9 und field10 sind Indizes anderes Tabellen.

    Zeig uns mal bitte deine Tabellenstruktur. Bei deinem SELECT wird ja nur von einer Tabelle selektiert.

    Wenn du Sachen im Vorfeld ausschließen möchtest, geht das natürlich mit einem SUBSELECT.

    Quellcode

    1. SELECT * FROM (
    2. SELECT *
    3. FROM table
    4. WHERE `field7` BETWEEN '2011-09-15 01:54:00' AND '2011-09-14 13:13:00'
    5. AND TIMEDIFF(field7, field8) < '12:00:00'
    6. ) x
    7. WHERE (field9=1 AND field10 LIKE 'aaa%')
    8. OR (field9=1 AND field10 LIKE 'bbb%')
    9. ..
    10. OR (field9=11 AND field10 LIKE 'hhh%')
    11. ORDER BY field3
    Alles anzeigen


    Folgenden Index würde ich empfehlen

    Quellcode

    1. ALTER TABLE table ADD INDEX `search` (field7, field9, field10, field3);
  • Torben Brodt schrieb:

    einheitswurzel schrieb:

    Ich bin nicht sicher ob ich dich richtig verstehe. Index ist field1 für die Tabelle. field9 und field10 sind Indizes anderes Tabellen.

    Zeig uns mal bitte deine Tabellenstruktur. Bei deinem SELECT wird ja nur von einer Tabelle selektiert.

    Wenn du Sachen im Vorfeld ausschließen möchtest, geht das natürlich mit einem SUBSELECT.

    Quellcode

    1. SELECT * FROM (
    2. SELECT *
    3. FROM table
    4. WHERE `field7` BETWEEN '2011-09-15 01:54:00' AND '2011-09-14 13:13:00'
    5. AND TIMEDIFF(field7, field8) < '12:00:00'
    6. ) x
    7. WHERE (field9=1 AND field10 LIKE 'aaa%')
    8. OR (field9=1 AND field10 LIKE 'bbb%')
    9. ..
    10. OR (field9=11 AND field10 LIKE 'hhh%')
    11. ORDER BY field3
    Alles anzeigen


    Folgenden Index würde ich empfehlen

    Quellcode

    1. ALTER TABLE table ADD INDEX `search` (field7, field9, field10, field3);



    Ich kann leider aus Sicherheitsgründen die Tabellenstruktur nicht 1:1 ins Forum posten. Aber das ist auch wirklich nicht nötig, denn es ist in der Tat nur eine einzige Tabelle, in der wie du richtig gesehen hast, field7,9,10 ud 3 zum Ordnen und Durchsuchen verwendet werden.

    Das Indizieren hat Wunder bewirkt (Geschwindigkeitssteigerung von 300%). Danke! Ich werde mich mal tiefer in die Thematik Indizes einlesen.

    Dennoch hab' ich da noch ein Dorn im Auge. Es kommen jeden Tag ca. 300 Datensätze hinzu. Aktuell sind es 60.000. Dadurch wird das Problem auch nicht kleiner. Wenn ich das richtig verstehe - ich denk mal schon ;) - werden bei meinem UNION-Konstrukt die 60.000 Einträge ca. 99 mal neudurchsucht - wie bitte?!?!
    Das heißt auch, dass ich 6.000.000 mal aus der Tabelle nur mit

    Quellcode

    1. `field7` BETWEEN '2011-09-15 01:54:00' AND '2011-09-14 13:13:00'


    extrahiere und damit weiterarbeite. Das könnte man ja im Vorfeld einmal machen und dann ists damit durch - Wie das ja auch oben schon mit dem SUBSELECT vorschlägst.

    Was du oben vorgeschlagen hast, finde ich schonmal toll. Nur habe ich da das Problem, dass nicht "nur die 5 Datensätze mit den höchsten Werten in 'field3' aus allen, die als field9/field10 Kombination '3'/'#aaa' haben" und "nur die 7 Datensätze mit den höchsten Werten in 'field3' aus allen, die als field9/field10 Kombination '2'/'#bbb' haben" usw. etc. pp.
    ausgewählt werden. Deshalb benötige ich ja das UNION-Konstrukt (oder etwas besseres).

    Kann ich nicht irgendwie das Ergebnis von

    SQL-Abfrage

    1. SELECT * FROM (
    2. SELECT *
    3. FROM table
    4. WHERE `field7` BETWEEN '2011-09-15 01:54:00' AND '2011-09-14 13:13:00'
    5. AND TIMEDIFF(field7, field8) < '12:00:00'
    6. )


    in eine Art Variable schreiben und dann mein UNION-Konstrukt über die eigentliche Tabelle, sondern über diese Vorauswahl in dieser Variablen laufen lassen?


    Grüße,

    EHW
  • Hm, also wenn wir das Problem abstrakt halten, dann geht es darum die Queries für die gegebene Architektur zu optimieren.
    Wenn man mehr müsste, würde man vielleicht eine ganz andere Architektur wählen?

    Also im selben Query mehrfach auf das Subselect zuzugreifen geht nicht. Aber du kannst es natürlich auch anders lösen.

    Ansatz1:
    Du kannst dir eine VIEW für das SUBSELECT anlegen.
    Das hilft dir aber nichts, wenn die Zeitstempel häufig ändern. So lange du sie aber relativ zu einem anderen Zeitpunkt ausdrücken kannst, wäre das kein Problem.

    Ansatz2:
    Zerlege das ganze in mehrere Prozesse:

    Prozess 1: TEMPORARY TABLE anlegen

    Quellcode

    1. CREATE TEMPORARY TABLE foo500
    2. SELECT * FROM (
    3. SELECT *
    4. FROM TABLE
    5. WHERE `field7` BETWEEN '2011-09-15 01:54:00' AND '2011-09-14 13:13:00'
    6. AND TIMEDIFF(field7, field8) < '12:00:00'
    7. ) x;
    8. ALTER TABLE foo500 ADD INDEX `search` (field7, field9, field10, field3);


    Prozess2: Auf TEMPORARY TABLE zugreifen

    Quellcode

    1. SELECT und UNION umstellen auf foo500


    Prozess3: TEMPORARY TABLE wieder löschen

    Weiterer Vorteil:
    Die TEMPORARY TABLE brauchst du vielleicht nur in gewissen Zeitabständen zu aktualisieren?
    Dann solltest du das TEMPORARY weglassen, denn TEMPORARY TABLES gelten nur für eine Verbindung, und mehrere Benutzer/Apache Prozesse machen mehrere Verbindungen auf.