SELECT-Abfrage optimieren

pz6j89 · 14.04.2008, 10:45

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

pz6j89 · 14.04.2008, 10:49

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

HPR1974 · 14.04.2008, 21:35

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

HPR1974 · 14.04.2008, 21:36

achja und wirf die subselects raus

David · 17.04.2008, 10:33

Und benutze mal EXPLAIN zum Auswerten des Query. Ich glaube (weiß es aber nicht genau), dass zum Beispiel

Zitat:

KEY `Index_TimestampThreadId` (`Timestamp`,`ThreadId`)

falsch herum definiert wurde für die Abfrage.
MySQL :: MySQL 5.0 Reference Manual :: 12.3.2 EXPLAIN Syntax

pz6j89 · 17.04.2008, 11:32

Zitat:

Zitat von David

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

David · 17.04.2008, 12:16

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

pz6j89 · 17.04.2008, 12:45

Mehr als hier zeigt er nicht an...

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

David · 17.04.2008, 13:27

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

pz6j89 · 17.04.2008, 13:48

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

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
Performance bei select abfrage	Diet	Datenbanken	4	14.05.2006 21:20
Mysql SELECT Abfrage -- Problem mit LIMIT	djrace	Datenbanken	2	01.05.2006 12:58
SELECT WHERE datetime abfrage - Optimierung	mrSpok	Datenbanken	15	23.03.2006 19:08
[Erledigt] select Abfrage		Datenbanken	6	07.02.2006 17:32
[Erledigt] SELECT - Abfrage		Datenbanken	2	04.01.2006 09:48
Im Select eine IFF Abfrage	bendigo	Datenbanken	4	21.11.2005 14:11
Select Abfrage	phpdummi	PHP Tipps 2005-2	5	11.10.2005 17:01
Select Abfrage mit where-clause + if-Restriktion		Datenbanken	4	25.07.2005 03:46
SELECT Abfrage.... seh den wald nich...	center	Datenbanken	8	01.06.2005 14:32
hochkommas in select abfrage.. ich raffe es nicht.	hekto	PHP Tipps 2005	31	22.01.2005 18:03
[Erledigt] [gelöst] MySQL abfrage eingrenzen anhand einer SELECT Auswah		Datenbanken	13	01.12.2004 18:42
Select optimieren oder anpassen		Datenbanken	21	21.09.2004 20:45
[Erledigt] 2 mal select 1 form		PHP-Fortgeschrittene	1	01.09.2004 20:13
Select abfrage / Insert	stefan-miti	PHP Tipps 2004	10	15.08.2004 20:35
Select abfrage in Schleife		PHP Tipps 2004	2	20.07.2004 15:37


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

14.04.2008, 21:35
HPR1974 Erfahrener Benutzer Registriert seit: 28.03.2008 Beiträge: 1.847	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

14.04.2008, 21:36
HPR1974 Erfahrener Benutzer Registriert seit: 28.03.2008 Beiträge: 1.847	achja und wirf die subselects raus

17.04.2008, 12:16
David Erfahrener Benutzer Registriert seit: 05.09.2007 Beiträge: 5.044	Oh, "using temporary". Kannst Du bitte nochmal ein EXPLAIN EXTENDED ... laufen lassen damit wir sehen können, wie MySQL die Query umschreibt?

17.04.2008, 12:45
pz6j89 Neuer Benutzer Registriert seit: 14.04.2008 Beiträge: 9	Mehr als hier zeigt er nicht an... http://www.webmasterpro.de/coding/fo...e_explain_.jpg

17.04.2008, 13:27
David Erfahrener Benutzer Registriert seit: 05.09.2007 Beiträge: 5.044	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

17.04.2008, 13:48
pz6j89 Neuer Benutzer Registriert seit: 14.04.2008 Beiträge: 9	wie kann ich das genau im MySQL Query Browser ausführen?