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:
Und hier meine derzeit aktuellste Abfrage:
Dazu gehört auch die folgende Funktion:
Ich hoffe das ich genügend Informationen zur Verfügung gestellt habe und das ihr mir helfen könnt.
Gruß Oli
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;
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;
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
Gruß Oli
Kommentar