Guido Schürkötter: MySQL: sehr lange Query kürzen, optimieren?

SELF-Forum

MySQL: sehr lange Query kürzen, optimieren?

Guido Schürkötter
– Informationen zu den Bewertungsregeln

Hallo Community,

ich habe ein Problem mit sehr langen SQL Queries, die meine Datenbank sehr lange beschäftigen. Die Query wird von einer Software so generiert und ich frage mich, ob es nicht auch kürzer geht. An der grundsätzlichen Datenstruktur kann ich leider nichts ändern, aber es wäre toll, wenn ein SQL Kenner mal kurz über die unten gezeigte SQL schauen könnte. Mir ginge es darum, ob man die Anfrage selbst anders formulieren könnte, damit sie kürzer ist bzw. idealerweise schneller verarbeitet wird. Ich denke man erkennt, dass ich selbst nicht den Plan habe ;)

Ich bedanke mich schonmal im Vorfeld für jede Hilfe. Falls ich woanders kompetenter Hilfe erhalten kann, würde ich mich über einen entsprechenden Link auch freuen.

Guido

SELECT audio.id , audio.audio\_file , IF( FIND_IN_SET( 955, audio.keys )
OR FIND_IN_SET( 956, audio.keys )
OR FIND_IN_SET( 3344, audio.keys )
OR FIND_IN_SET( 3349, audio.keys )
OR FIND_IN_SET( 3619, audio.keys )
OR FIND_IN_SET( 3620, audio.keys )
OR FIND_IN_SET( 5210, audio.keys )
OR FIND_IN_SET( 5329, audio.keys )
OR FIND_IN_SET( 6209, audio.keys )
OR FIND_IN_SET( 6796, audio.keys )
OR FIND_IN_SET( 8506, audio.keys )
OR FIND_IN_SET( 9808, audio.keys )
OR FIND_IN_SET( 9890, audio.keys )
OR FIND_IN_SET( 9891, audio.keys )
OR FIND_IN_SET( 9893, audio.keys )
OR FIND_IN_SET( 10607, audio.keys )
OR FIND_IN_SET( 11809, audio.keys )
OR FIND_IN_SET( 12494, audio.keys )
OR FIND_IN_SET( 13665, audio.keys )
OR FIND_IN_SET( 13672, audio.keys )
OR FIND_IN_SET( 15535, audio.keys )
OR FIND_IN_SET( 17545, audio.keys )
OR FIND_IN_SET( 18257, audio.keys )
OR FIND_IN_SET( 19000, audio.keys )
OR FIND_IN_SET( 20366, audio.keys )
OR FIND_IN_SET( 21347, audio.keys )
OR FIND_IN_SET( 22672, audio.keys )
OR FIND_IN_SET( 22864, audio.keys )
OR FIND_IN_SET( 22889, audio.keys )
OR FIND_IN_SET( 23357, audio.keys )
OR FIND_IN_SET( 26076, audio.keys )
OR FIND_IN_SET( 26394, audio.keys )
OR FIND_IN_SET( 28481, audio.keys )
OR FIND_IN_SET( 28774, audio.keys )
OR FIND_IN_SET( 31526, audio.keys )
OR FIND_IN_SET( 38525, audio.keys )
OR FIND_IN_SET( 39045, audio.keys )
OR FIND_IN_SET( 42062, audio.keys )
OR FIND_IN_SET( 44699, audio.keys )
OR FIND_IN_SET( 46021, audio.keys ) , 1, IF( FIND_IN_SET( 955, audio.keyword )
OR FIND_IN_SET( 956, audio.keyword )
OR FIND_IN_SET( 3344, audio.keyword )
OR FIND_IN_SET( 3349, audio.keyword )
OR FIND_IN_SET( 3619, audio.keyword )
OR FIND_IN_SET( 3620, audio.keyword )
OR FIND_IN_SET( 5210, audio.keyword )
OR FIND_IN_SET( 5329, audio.keyword )
OR FIND_IN_SET( 6209, audio.keyword )
OR FIND_IN_SET( 6796, audio.keyword )
OR FIND_IN_SET( 8506, audio.keyword )
OR FIND_IN_SET( 9808, audio.keyword )
OR FIND_IN_SET( 9890, audio.keyword )
OR FIND_IN_SET( 9891, audio.keyword )
OR FIND_IN_SET( 9893, audio.keyword )
OR FIND_IN_SET( 10607, audio.keyword )
OR FIND_IN_SET( 11809, audio.keyword )
OR FIND_IN_SET( 12494, audio.keyword )
OR FIND_IN_SET( 13665, audio.keyword )
OR FIND_IN_SET( 13672, audio.keyword )
OR FIND_IN_SET( 15535, audio.keyword )
OR FIND_IN_SET( 17545, audio.keyword )
OR FIND_IN_SET( 18257, audio.keyword )
OR FIND_IN_SET( 19000, audio.keyword )
OR FIND_IN_SET( 20366, audio.keyword )
OR FIND_IN_SET( 21347, audio.keyword )
OR FIND_IN_SET( 22672, audio.keyword )
OR FIND_IN_SET( 22864, audio.keyword )
OR FIND_IN_SET( 22889, audio.keyword )
OR FIND_IN_SET( 23357, audio.keyword )
OR FIND_IN_SET( 26076, audio.keyword )
OR FIND_IN_SET( 26394, audio.keyword )
OR FIND_IN_SET( 28481, audio.keyword )
OR FIND_IN_SET( 28774, audio.keyword )
OR FIND_IN_SET( 31526, audio.keyword )
OR FIND_IN_SET( 38525, audio.keyword )
OR FIND_IN_SET( 39045, audio.keyword )
OR FIND_IN_SET( 42062, audio.keyword )
OR FIND_IN_SET( 44699, audio.keyword )
OR FIND_IN_SET( 46021, audio.keyword ) , 2, IF( FIND_IN_SET( 955, audio.keys )
OR FIND_IN_SET( 956, audio.keys )
OR FIND_IN_SET( 3344, audio.keys )
OR FIND_IN_SET( 3349, audio.keys )
OR FIND_IN_SET( 3619, audio.keys )
OR FIND_IN_SET( 3620, audio.keys )
OR FIND_IN_SET( 5210, audio.keys )
OR FIND_IN_SET( 5329, audio.keys )
OR FIND_IN_SET( 6209, audio.keys )
OR FIND_IN_SET( 6796, audio.keys )
OR FIND_IN_SET( 8506, audio.keys )
OR FIND_IN_SET( 9808, audio.keys )
OR FIND_IN_SET( 9890, audio.keys )
OR FIND_IN_SET( 9891, audio.keys )
OR FIND_IN_SET( 9893, audio.keys )
OR FIND_IN_SET( 10607, audio.keys )
OR FIND_IN_SET( 11809, audio.keys )
OR FIND_IN_SET( 12494, audio.keys )
OR FIND_IN_SET( 13665, audio.keys )
OR FIND_IN_SET( 13672, audio.keys )
OR FIND_IN_SET( 15535, audio.keys )
OR FIND_IN_SET( 17545, audio.keys )
OR FIND_IN_SET( 18257, audio.keys )
OR FIND_IN_SET( 19000, audio.keys )
OR FIND_IN_SET( 20366, audio.keys )
OR FIND_IN_SET( 21347, audio.keys )
OR FIND_IN_SET( 22672, audio.keys )
OR FIND_IN_SET( 22864, audio.keys )
OR FIND_IN_SET( 22889, audio.keys )
OR FIND_IN_SET( 23357, audio.keys )
OR FIND_IN_SET( 26076, audio.keys )
OR FIND_IN_SET( 26394, audio.keys )
OR FIND_IN_SET( 28481, audio.keys )
OR FIND_IN_SET( 28774, audio.keys )
OR FIND_IN_SET( 31526, audio.keys )
OR FIND_IN_SET( 38525, audio.keys )
OR FIND_IN_SET( 39045, audio.keys )
OR FIND_IN_SET( 42062, audio.keys )
OR FIND_IN_SET( 44699, audio.keys )
OR FIND_IN_SET( 46021, audio.keys ) , 3, IF( 0, 4, 9 ) ) ) ) AS relevanz
FROM audio
JOIN structure\_audio ON structure\_audio.id = audio.id
WHERE (
structure\_audio.id IS NOT NULL
AND audio.state =1
)
AND (
(
FIND_IN_SET( 955, audio.keys )
OR FIND_IN_SET( 956, audio.keys )
OR FIND_IN_SET( 3344, audio.keys )
OR FIND_IN_SET( 3349, audio.keys )
OR FIND_IN_SET( 3619, audio.keys )
OR FIND_IN_SET( 3620, audio.keys )
OR FIND_IN_SET( 5210, audio.keys )
OR FIND_IN_SET( 5329, audio.keys )
OR FIND_IN_SET( 6209, audio.keys )
OR FIND_IN_SET( 6796, audio.keys )
OR FIND_IN_SET( 8506, audio.keys )
OR FIND_IN_SET( 9808, audio.keys )
OR FIND_IN_SET( 9890, audio.keys )
OR FIND_IN_SET( 9891, audio.keys )
OR FIND_IN_SET( 9893, audio.keys )
OR FIND_IN_SET( 10607, audio.keys )
OR FIND_IN_SET( 11809, audio.keys )
OR FIND_IN_SET( 12494, audio.keys )
OR FIND_IN_SET( 13665, audio.keys )
OR FIND_IN_SET( 13672, audio.keys )
OR FIND_IN_SET( 15535, audio.keys )
OR FIND_IN_SET( 17545, audio.keys )
OR FIND_IN_SET( 18257, audio.keys )
OR FIND_IN_SET( 19000, audio.keys )
OR FIND_IN_SET( 20366, audio.keys )
OR FIND_IN_SET( 21347, audio.keys )
OR FIND_IN_SET( 22672, audio.keys )
OR FIND_IN_SET( 22864, audio.keys )
OR FIND_IN_SET( 22889, audio.keys )
OR FIND_IN_SET( 23357, audio.keys )
OR FIND_IN_SET( 26076, audio.keys )
OR FIND_IN_SET( 26394, audio.keys )
OR FIND_IN_SET( 28481, audio.keys )
OR FIND_IN_SET( 28774, audio.keys )
OR FIND_IN_SET( 31526, audio.keys )
OR FIND_IN_SET( 38525, audio.keys )
OR FIND_IN_SET( 39045, audio.keys )
OR FIND_IN_SET( 42062, audio.keys )
OR FIND_IN_SET( 44699, audio.keys )
OR FIND_IN_SET( 46021, audio.keys )
)
OR (
FIND_IN_SET( 955, audio.keyword )
OR FIND_IN_SET( 956, audio.keyword )
OR FIND_IN_SET( 3344, audio.keyword )
OR FIND_IN_SET( 3349, audio.keyword )
OR FIND_IN_SET( 3619, audio.keyword )
OR FIND_IN_SET( 3620, audio.keyword )
OR FIND_IN_SET( 5210, audio.keyword )
OR FIND_IN_SET( 5329, audio.keyword )
OR FIND_IN_SET( 6209, audio.keyword )
OR FIND_IN_SET( 6796, audio.keyword )
OR FIND_IN_SET( 8506, audio.keyword )
OR FIND_IN_SET( 9808, audio.keyword )
OR FIND_IN_SET( 9890, audio.keyword )
OR FIND_IN_SET( 9891, audio.keyword )
OR FIND_IN_SET( 9893, audio.keyword )
OR FIND_IN_SET( 10607, audio.keyword )
OR FIND_IN_SET( 11809, audio.keyword )
OR FIND_IN_SET( 12494, audio.keyword )
OR FIND_IN_SET( 13665, audio.keyword )
OR FIND_IN_SET( 13672, audio.keyword )
OR FIND_IN_SET( 15535, audio.keyword )
OR FIND_IN_SET( 17545, audio.keyword )
OR FIND_IN_SET( 18257, audio.keyword )
OR FIND_IN_SET( 19000, audio.keyword )
OR FIND_IN_SET( 20366, audio.keyword )
OR FIND_IN_SET( 21347, audio.keyword )
OR FIND_IN_SET( 22672, audio.keyword )
OR FIND_IN_SET( 22864, audio.keyword )
OR FIND_IN_SET( 22889, audio.keyword )
OR FIND_IN_SET( 23357, audio.keyword )
OR FIND_IN_SET( 26076, audio.keyword )
OR FIND_IN_SET( 26394, audio.keyword )
OR FIND_IN_SET( 28481, audio.keyword )
OR FIND_IN_SET( 28774, audio.keyword )
OR FIND_IN_SET( 31526, audio.keyword )
OR FIND_IN_SET( 38525, audio.keyword )
OR FIND_IN_SET( 39045, audio.keyword )
OR FIND_IN_SET( 42062, audio.keyword )
OR FIND_IN_SET( 44699, audio.keyword )
OR FIND_IN_SET( 46021, audio.keyword )
)
OR (
(
( 0 )
OR ( 0 )
OR ( 0 )
OR audio.audio\_number = 'drums'
)
)
)
ORDER BY relevanz ASC , audio_quality DESC , audio.create DESC
LIMIT 0 , 3000

Beitrag melden
–
Informationen zu den Bewertungsregeln

  1. MySQL: sehr lange Query kürzen, optimieren?

    Frank (no reg)
    – Informationen zu den Bewertungsregeln

    Hi,

    hmmm, das hängt vielleicht auch ein wenig davon ab, woher du die Zahlen 39045, 46021 usw beziehst.

    Aber die Formulierung der Abfrage und die Verwendung von FIND_IN_SET() deutet auf ein ziemlich mässiges Datendesign hin....

    Besonders interessant finde ich:

    .... OR (
    (
    ( 0 )
    OR ( 0 )
    OR ( 0 ) ....

    Nixdestotrotz würde ich sagen, ohne genauere Kenntnis deines Datendesigns und der Anwendung (was die Abfrage für ein Ergebnis liefern soll) lässt sich pauschal nicht unbedingt viel Rat geben.

    Womit hast du bei der Abfrage ein Problem?

    • zu lang zum selbst schreiben
        -> ich dachte, das macht sowieso das Programm und den Benutzer brauch es nicht interessieren

    • langsam
        -> evt solltest du dann mal über Volltextindizes nachdenken, die könnten u.U. helfen und den Lookup-Prozess in audio.keyword verschnellern

    Ciao, Frank

    Beitrag melden
    –
    Informationen zu den Bewertungsregeln

    1. MySQL: sehr lange Query kürzen, optimieren?

      Sven Rautenberg Homepage des Autors
      – Informationen zu den Bewertungsregeln

      Moin!

      hmmm, das hängt vielleicht auch ein wenig davon ab, woher du die Zahlen 39045, 46021 usw beziehst.

      Aber die Formulierung der Abfrage und die Verwendung von FIND_IN_SET() deutet auf ein ziemlich mässiges Datendesign hin....

      Da stimme ich eindeutig zu.

      Heißt: Die Ausführungsdauer läßt sich nur optimieren, wenn die Datenstruktur optimiert wird. Derzeit lagern diverse "Schlüsselzahlen" alle in einem einzigen String innerhalb des jeweiligen Datensatzes, und der Query sucht nach jeder gewünschten Zahl einzeln. Das sind massenweise Stringoperationen, die jeweils immer wieder bei Null anfangen.

      Optimiert wäre, wenn diese Schlüsselzahlen in einer separaten Tabelle lagern würden, und von dort mit dem Datensatz verknüpft sind. Dann könnte man diverse Optimierungsstrategien wie Indexe oder Einkürzung der langen OR-Kette zu einem einzigen IN-Operator anwenden.

      • langsam
          -> evt solltest du dann mal über Volltextindizes nachdenken, die könnten u.U. helfen und den Lookup-Prozess in audio.keyword verschnellern

      Ein Volltextindex muß aber auch speziell benutzt werden, er wird nicht automatisch eingesetzt. Siehe http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html.

      - Sven Rautenberg

      --
      "Love your nation - respect the others."
      Beitrag melden
      –
      Informationen zu den Bewertungsregeln

      1. MySQL: sehr lange Query kürzen, optimieren?

        Guido Schürkötter
        – Informationen zu den Bewertungsregeln

        Danke Euch beiden für die schnelle Antwort.
        Prinzipiell habe ich die Antwort bekommen, die ich vorher erwartet hatte. Das Problem ist, die Datenbankstruktur _ist_ grottig, Ihr habt völlig Recht. Wir entwickeln inzwischen ein völlig anderes System, welches genau die Ansätze der Optimierung verfolgt, die mir hier geraten werden.

        Nur leider müssen wir uns noch ein paar Monate gedulden und mit dem aktuellen System vorlieb nehmen. Dieses umzustellen wäre wirtschaftlich nicht möglich.

        Leider sind die Datenbankanfragen nun aber wirklich spürbar langsamer durch die vielen Informationen und das Benutzen von FIND_IN_SET.

        Nun ist also meine einzige Hoffnung, ob man die SQL noch etwas optimieren kann (wenn es einen Ansatz gäbe dann würde es sich rentieren einen Programmierer dranzusetzen, um die Zeit bis zum neuen System erträglicher zu machen).

        Daher meine Überlegung, ob die SQL selbst noch irgendwie optimiert werden kann. Den Hinweis auf einen Volltextindex finde ich z.B. sehr interessant.
        Und vielleicht kann man durch sonstige Tricks noch etwas Performance rausschlagen.

        So ich hoffe das macht es etwas klarer. Ich hätte es gleich ins erste Posting reinschreiben sollen.

        Was sagt Ihr? Habt Ihr noch eine Idee?
        Danke, Guido

        Beitrag melden
        –
        Informationen zu den Bewertungsregeln

        1. MySQL: sehr lange Query kürzen, optimieren?

          Guido Schürkötter
          – Informationen zu den Bewertungsregeln

          Ok, kleiner Nachtrag: zumindest die MySQL Tabellenfelder die relevant sind stehen auf Fulltext. Wenn ich das richtig verstehe muss aber die SQL noch entsprechend angepasst werden?

          Guido

          Beitrag melden
          –
          Informationen zu den Bewertungsregeln

          1. MySQL: sehr lange Query kürzen, optimieren?

            ChrisB
            – Informationen zu den Bewertungsregeln

            Hi,

            Ok, kleiner Nachtrag: zumindest die MySQL Tabellenfelder die relevant sind stehen auf Fulltext. Wenn ich das richtig verstehe muss aber die SQL noch entsprechend angepasst werden?

            Wenn du mittels eines FULLTEXT-Index suchen willst, musst du MATCH(namen_indizierter_spalten) AGAINST(suchbegriff) fuer die Vergleiche verwenden - siehe MySQL Manual, 11.8. Full-Text Search Functions.

            Aber ich bezweifle, dass dir das hier ueberhaupt etwas bringt.
            Zum einen hast du in deinem Beispielcode auch Ziffernkombinationen, die nur dreistellig sind - alles, was kuerzer als 4 "word characters" ist, wird aber in Defaulteinstellung aber nicht indiziert (daran koennte man, bei Zugriff auf die MySQL-Server-Konfiguration, zwar drehen - aber es wirkt sich natuerlich weiter zu Ungunsten der Performance aus).
            Und zum anderen werden Suchbegriffe, die in mehr als 50% der Datensaetze vorkommen, ignoriert, liefern keinen Treffer - wenn also manche deiner Ziffernkombinationen dieses Kriterium erfuellen, bekaemst du damit ebenfalls Probleme.

            MfG ChrisB

            Beitrag melden
            –
            Informationen zu den Bewertungsregeln

            1. MySQL: sehr lange Query kürzen, optimieren?

              Guido
              – Informationen zu den Bewertungsregeln

              Puh. Sieht nach ner Einbahnstrasse aus. Danke für die interessante Info.

              Guido.

              Beitrag melden
              –
              Informationen zu den Bewertungsregeln