SELECT-Abfrage optimieren

[pz6j89]

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 musste ich wegen der Länge des Beitrages als Anhang beifügen.

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:

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:

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:

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

[|Coding]

Hi!

Du könntest mal nachsehen, ob Du vielleicht noch Indizes hinzufügen kannst.

Nutze dazu: EXPLAIN Dein_SQL_Statement

[pz6j89]

Ich habe das jetzt mal ausgeführt und den Screenshot hier mal angehängt. Also ich glaube das ich keine zusätzlichen Indizies mehr benötige.