| pz6j89 |
14.04.2008 10:28:13 |
SELECT-Abfrage optimieren
Liste der Anhänge anzeigen (Anzahl: 1)
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:
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 |