Ankündigung

Einklappen
Keine Ankündigung bisher.

SELECT-Abfrage optimieren

Einklappen

Neue Werbung 2019

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

  • SELECT-Abfrage optimieren

    Hallo.

    Ich habe die Aufgabe erhalten, die SQL-Anweisungen auf einer stark frequentierten Internetseite zu optimieren. Mein Problem ist eine einzelne SELECT-Abfrage anzupassen. Diese funktioniert bei kleinen Datenmengen (bis 50.000 Datensätze pro involvierte Tabelle) bis auf kleine Performanceprobleme gut. Allerdings haben wir in einer Tabelle von den 5 ca. 600.000 Datensätze. Das Tabellenschema steht in der nächsten Antwort.

    Die beiden Tabellen "document" und "lng" sind nicht für das Problem verantwortlich und wurden daher von mir nur der Vollständigkeit wegen (ohne Details) angegeben.

    Die alte Abfrage versagte. Durch verschiedene Testreihen (entfernen einzelner Bestandteile aus der Abfrage) konnte ich feststellen dass das Problem folgendes ist:

    Und zwar muss aus der Tabelle "user" der Benutzername (LoginName) mit Hilfe der in der Tabelle "forumthreadentry" eingetragenen Benutzerid (AuthorId) ausgelsen werden. Dies dauert einfach viel zu lange (bis zu 3 Minuten).

    Noch zur weiteren Erklärung: Diese Abfrage zeigt die Übersicht der in einem Topic verfassten Threads an. Diese Threads werden nach der letzten geschriebenen Antwort chronologisch absteigend sortiert. Pro Seite werden 30 Threads angezeigt (dieser Umstand wirkt sich aber nicht auf die Zeit aus. Die Zeit bleibt gleich ob mit oder ohne LIMIT).

    Außerdem wäre es schön die ganzen SUBSELECTS aus der Abfrage zu verbannen (sofern möglich).

    Hier die alte Abfrage:
    Code:
    SELECT
      forumthread.Id
      ,forumthread.Locked
      ,forumthread.SolvedState
      ,forumthread.Timestamp
      ,forumthread.Title
      ,forumthread.ViewCount
      ,forumthreadentry.AllowHtml
      ,forumthreadentry.ImageId
      ,forumthreadentry.Text
      ,forumthreadentry.Timestamp
      ,(SELECT
          COUNT(forumthreadentryCount.Id)
        FROM
          forumthreadentry AS forumthreadentryCount
        WHERE
          forumthreadentryCount.ThreadId = forumthread.Id
        LIMIT 1) AS forumthreadentryCount
      ,(SELECT
          COUNT(forumthreadentryCount2.Id)
        FROM
          forumthreadentry AS forumthreadentryCount2
        WHERE
          forumthreadentryCount2.AuthorId = @userId
        AND
          forumthreadentryCount2.ThreadId = forumthread.Id
       ) AS forumthreadentryCount2
      ,forumthreadentryUser.Id
      ,forumthreadentryUser.LoginName
      ,forumthreadUser.Id
      ,forumthreadUser.LoginName
      ,forumthread.LngId AS LngId
      ,flagDocument.Id AS LngFlagId
      ,flagDocument.Extension AS LngFlagExtension
      ,forumthreadentrylast.Timestamp AS LastEntryTimeStamp
    
    FROM
      forumthread
    
    LEFT JOIN
      forumthreadentry AS forumthreadentrylast
    ON
      forumthreadentrylast.Id = (SELECT
                                   forumthreadentryUserSelect.Id
                                 FROM
                                   forumthreadentry AS forumthreadentryUserSelect
                                 WHERE
                                   forumthreadentryUserSelect.ThreadId = forumthread.Id
                                 ORDER BY
                                   forumthreadentryUserSelect.Timestamp DESC
                                 LIMIT 1)
    
    LEFT JOIN
      forumthreadentry
    ON
      forumthreadentry.ThreadId = forumthread.Id
    
    LEFT JOIN
      user AS forumthreadentryUser
    ON
      forumthreadentryUser.Id = forumthreadentrylast.AuthorId
    
    JOIN
      user AS forumthreadUser
    ON
      forumthreadUser.Id = forumthread.AuthorId
    
    LEFT JOIN
      lng AS forumthreadLng
    ON
      forumthreadLng.Id = forumthread.LngId
    
    LEFT JOIN
      document AS flagDocument
    ON flagDocument.Id = forumthreadLng.SmallFlagImage
    
    WHERE
      forumthread.TopicId = @forumTopicId
    
    GROUP BY
      forumthread.Id
    
    ORDER BY
      forumthreadentrylast.Timestamp DESC
      ,forumthread.Timestamp DESC
    
    LIMIT
      0
      ,30;
    Und hier meine derzeit aktuellste Abfrage:
    Code:
    SELECT
      forumthread.Id
      ,forumthread.Title
      ,forumthread.Locked
      ,forumthread.SolvedState
      ,forumthread.Timestamp AS Open_Timestamp
      ,MAX(forumthreadentry.Timestamp) AS LastPost_Timestamp
      ,forumthreadentry.AuthorId
      ,user.LoginName
      ,forumthread.ViewCount
      ,f_countthreads(forumthreadentry.ThreadId) AS counter2
      ,forumthreadentry.AllowHtml
      ,forumthreadentry.ImageId
      ,forumthread.LngId AS LngId
      ,document.Id AS LngFlagId
      ,document.Extension AS LngFlagExtension
    FROM
      forumthreadentry
    
    LEFT JOIN
      forumthread
    ON
      forumthread.Id = forumthreadentry.ThreadId
    
    LEFT JOIN
      user
    ON
      user.Id = forumthreadentry.AuthorId
    
    LEFT JOIN
      lng
    ON
      lng.Id = forumthread.LngId
    
    LEFT JOIN
      document
    ON
      document.Id = lng.SmallFlagImage
    
    WHERE
      forumthread.TopicId = @forumTopicId
    GROUP BY
      forumthreadentry.ThreadId
    
    ORDER BY
      LastPost_Timestamp DESC
    
    LIMIT
      0
      ,30;
    Dazu gehört auch die folgende Funktion:
    Code:
    DELIMITER $$
    
    DROP FUNCTION IF EXISTS `f_countthreads` $$
    CREATE DEFINER=`root`@`localhost` FUNCTION `f_countthreads`(ThreadId INT) RETURNS int(11)
    BEGIN
      DECLARE counter INT;
      SELECT
               COUNT(forumthreadentry.ThreadId) INTO counter
             FROM
               forumthreadentry
             WHERE
               forumthreadentry.ThreadId = ThreadId
             LIMIT 1;
      RETURN counter;
    END $$
    
    DELIMITER
    Ich hoffe das ich genügend Informationen zur Verfügung gestellt habe und das ihr mir helfen könnt.

    Gruß Oli

  • #2
    Hier das Tabellenschema:
    Code:
    Tabelle: forumthread (ENGINE=InnoDB DEFAULT CHARSET=latin1)
    ===========================================================
    Datensätze: ca. 56.000
    
    |---------------------------------------------------------------|
    | Spalte      | Datentyp u. Extras                              |
    |-------------+-------------------------------------------------|
    | Id          | int(10) unsigned NOT NULL auto_increment        |
    | Title       | text NOT NULL                                   |
    | AuthorId    | int(10) unsigned NOT NULL default '0'           |
    | Timestamp   | datetime NOT NULL default '2000-01-01 00:00:00' |
    | TopicId     | int(10) unsigned NOT NULL default '0'           |
    | ViewCount   | int(10) unsigned NOT NULL default '0'           |
    | Locked      | tinyint(1) unsigned NOT NULL default '0'        |
    | SolvedState | int(10) unsigned NOT NULL default '0'           |
    | LngId       | int(10) unsigned default NULL                   |
    |---------------------------------------------------------------|
    
      PRIMARY KEY  (`Id`)
      KEY `FK_forumthread_TopicId` (`TopicId`)
      KEY `FK_forumthread_AuthorId` (`AuthorId`)
      KEY `Index_Timestamp` (`Timestamp`)
      KEY `FK_forumthread_LngId` (`LngId`)
      CONSTRAINT `FK_forumthread_AuthorId` FOREIGN KEY (`AuthorId`) REFERENCES `user` (`Id`)
      CONSTRAINT `FK_forumthread_LngId` FOREIGN KEY (`LngId`) REFERENCES `lng` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
      CONSTRAINT `FK_forumthread_TopicId` FOREIGN KEY (`TopicId`) REFERENCES `forumtopic` (`Id`) ON DELETE CASCADE
    
    
    
    Tabelle: forumthreadentry (ENGINE=InnoDB DEFAULT CHARSET=latin1)
    ================================================================
    Datensätze: ca. 620.000
    
    |---------------------------------------------------------------|
    | Spalte      | Datentyp u. Extras                              |
    |-------------+-------------------------------------------------|
    | Id          | int(10) unsigned NOT NULL auto_increment        |
    | Text        | text NOT NULL                                   |
    | AuthorId    | int(10) unsigned NOT NULL default '0'           |
    | Timestamp   | datetime NOT NULL default '0000-00-00 00:00:00' |
    | ThreadId    | int(10) unsigned NOT NULL default '0'           |
    | ImageId     | int(10) unsigned default NULL                   |
    | AllowHtml   | tinyint(1) unsigned NOT NULL default '0'        |
    |---------------------------------------------------------------|
    
      PRIMARY KEY  (`Id`)
      KEY `FK_forumthreadentry_AuthorId` (`AuthorId`)
      KEY `FK_forumthreadentry_ThreadId` (`ThreadId`),
      KEY `Index_TimestampThreadId` (`Timestamp`,`ThreadId`)
      KEY `FK_forumthreadentry_ImageId` (`ImageId`)
      CONSTRAINT `FK_forumthreadentry_AuthorId` FOREIGN KEY (`AuthorId`) REFERENCES `user` (`Id`)
      CONSTRAINT `FK_forumthreadentry_ImageId` FOREIGN KEY (`ImageId`) REFERENCES `document` (`Id`)
      CONSTRAINT `FK_forumthreadentry_ThreadId` FOREIGN KEY (`ThreadId`) REFERENCES `forumthread` (`Id`) ON DELETE CASCADE
    
    
    
    Tabelle: user (ENGINE=InnoDB DEFAULT CHARSET=utf8)
    ==================================================
    Datensätze: ca. 17.000
    
    |------------------------------------------------------------------|
    | Spalte         | Datentyp u. Extras                              |
    |----------------+-------------------------------------------------|
    | Id             | int(10) unsigned NOT NULL auto_increment        |
    | LoginName      | text NOT NULL                                   |
    | PassHash       | int(10) unsigned NOT NULL default '0'           |
    | Active         | datetime NOT NULL default '0000-00-00 00:00:00' |
    | ImageId        | int(10) unsigned NOT NULL default '0'           |
    | Signature      | int(10) unsigned default NULL                   |
    | HomepageUrl    | int(10) unsigned default NULL                   |
    | AIMAdress      | int(10) unsigned default NULL                   |
    | EMailAdress    | int(10) unsigned default NULL                   |
    | SiteId         | int(10) unsigned default NULL                   |
    | Responsibility | int(10) unsigned default NULL                   |
    | Prio           | int(10) unsigned default NULL                   |
    | CreateDate     | int(10) unsigned default NULL                   |
    | ApproveKey     | tinyint(1) unsigned NOT NULL default '0'        |
    |------------------------------------------------------------------|
    
      PRIMARY KEY  (`Id`)
      UNIQUE KEY `Index_Name` USING BTREE (`LoginName`,`SiteId`)
      KEY `FK_user_ImageId` (`ImageId`)
      KEY `FK_user_SiteId` (`SiteId`)
      KEY `Index_Prio` (`Prio`)
      CONSTRAINT `FK_user_ImageId` FOREIGN KEY (`ImageId`) REFERENCES `document` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE
      CONSTRAINT `FK_user_SiteId` FOREIGN KEY (`SiteId`) REFERENCES `site` (`Id`)
    
    
    Tabelle: document (ENGINE=InnoDB DEFAULT CHARSET=utf8)
    ======================================================
    Datensätze: ca. 97.000
    
    Tabelleninhalt nicht relevant
    
    
    Tabelle: lng (ENGINE=InnoDB DEFAULT CHARSET=utf8)
    =================================================
    Datensätze: 3
    
    Tabelleninhalt nicht relevant

    Kommentar


    • #3
      als erstes würde ich Dir empfehlen, die left joins, wenn möglich durch echte Joins zu ersetzen.... d.h. über die WHERE clause verbinden. Du hast constraints benutzt, dann kannst Du auch davon ausgehen, dass werte vorhanden sind

      wenn der Join mit der Tabelle user der Flaschenhals ist zwei Dinge auf den ersten Blick:
      - das feld LoginName ist vom Typ text (muss das so sein?)
      - lass den Join zur Tabelle user mal weg und reichere Dein resultset nachgelagert mit den User Daten an, dabei aber nicht hunderte von queries nehmen sondern ein query das alle userdaten in ein array fetched, eventuell hier ein kleiner Join mit der threadstabelle, also nur userdaten holen, die in Frage kommen. Mit dem grossen array belastet Du dann zwar den memory des scripts, aber nicht so sehr das DB System

      Kommentar


      • #4
        achja und wirf die subselects raus

        Kommentar


        • #5
          Und benutze mal EXPLAIN zum Auswerten des Query. Ich glaube (weiß es aber nicht genau), dass zum Beispiel
          KEY `Index_TimestampThreadId` (`Timestamp`,`ThreadId`)
          falsch herum definiert wurde für die Abfrage.
          MySQL :: MySQL 5.0 Reference Manual :: 12.3.2 EXPLAIN Syntax

          Kommentar


          • #6
            Zitat von David Beitrag anzeigen
            Und benutze mal EXPLAIN zum Auswerten des Query. Ich glaube (weiß es aber nicht genau), dass zum Beispielfalsch herum definiert wurde für die Abfrage.
            MySQL :: MySQL 5.0 Reference Manual :: 12.3.2 EXPLAIN Syntax
            Ein EXPLAIN hab ich schon gemacht und der hat keine Fehler zurückgegeben. Ein Screenshot von dem EXPLAIN ist in dem nachfolgenden Forum in der 2 Antwort zu finden:
            Webmasterpro Forum - [SQL]SELECT-Abfrage optimieren - Webmasterpro.de

            Kommentar


            • #7
              Oh, "using temporary". Kannst Du bitte nochmal ein EXPLAIN EXTENDED ... laufen lassen damit wir sehen können, wie MySQL die Query umschreibt?

              Kommentar


              • #8
                Mehr als hier zeigt er nicht an...

                http://www.webmasterpro.de/coding/fo...e_explain_.jpg

                Kommentar


                • #9
                  Ach ja... das ist ja so eine Krücke bei MySQL.
                  erst EXPLAIN EXTENDED
                  und direkt danach als nächsten Befehl SHOW WARNINGS
                  Dann bekommt man die Umformungen angezeigt ...IIRC

                  Kommentar


                  • #10
                    wie kann ich das genau im MySQL Query Browser ausführen?

                    Kommentar


                    • #11
                      Genau wie jedes SELECT auch. Eingeben und abschicken. Hoffe ich jedenfalls, da ich das Programm nicht kenne

                      Kommentar


                      • #12
                        Also ich hab das jetzt ausgeführt und er gibt mir keine warnung zurück. mit der meldung "0 zeilen in xxx s geholt"

                        bzw. in dem screenshot findest du in dem abschnitt ganz unten das ganze select-statement so wie mysql es ausführt... hab nur den dbnamen immer geschwärzt

                        Kommentar


                        • #13
                          Dann bin ich leider ratlos was die Auswertung angeht.
                          Wenn insbesondere das Subquery Probleme macht, würde ich die entsprechenden Daten in einem Feld von forumthread speichern. Soweit ich es verstanden habe, geht es doch immer nur um den "letzten" Wert, der sich nur beim Verfassen eines neuen Beitrages ändert.

                          Kommentar


                          • #14
                            das sind aber redundanzen die ich verhindern will...

                            ich habe jetzt aber eine andere lösung gefunden

                            Kommentar


                            • #15
                              Redundanzen vermeiden und Geschwindigkeit stehen sich manchmal im Weg.
                              Wie ein Tutor von mir so richtig sagte: "Normalformen sind gut bis auf die Fälle, wo sie es nicht sind"

                              Kommentar

                              Lädt...
                              X