Trage Query

[ErikBooy007]

Goedemorgen!

Voor een project dat ik nog in ontwikkeling heb, gebruik ik twee tabellen in een database en een SELECT query met een JOIN tussen de twee. De query is alleen érg traag. De gemiddelde tijd voor de query om te draaien is 1,985 seconden op mijn lokale machine. Het doel van de query is om uit te zoeken van elke locatie in de "LOCATIONS" tabel hoeveel evenementen er nog in de "EVENTS" database zitten en wanneer het laatste evenement is. De samplegrootte van de tabellen is nu 1500 locaties en 5000 evenementen.

De tabellen zien er als volgt uit (iets gesimplificeerd):

LOCATIONS
[SQL]
CREATE TABLE `LOCATIONS` (
`LID` int(5) NOT NULL AUTO_INCREMENT,
`NAME` varchar(100) NOT NULL DEFAULT '',
`CITY` varchar(100) NOT NULL DEFAULT '',
`ACTIVE` tinyint(1) NOT NULL,
`LATEST_CHECK` date NOT NULL DEFAULT '2012-01-01',
PRIMARY KEY (`LID`)
) ENGINE=InnoDB AUTO_INCREMENT=1501 DEFAULT CHARSET=utf8;
[/SQL]

EVENTS
[SQL]
CREATE TABLE `EVENTS` (
`EID` int(10) NOT NULL AUTO_INCREMENT,
`LID` int(5) NOT NULL,
`EVENTDATE` date NOT NULL,
PRIMARY KEY (`EID`)
) ENGINE=InnoDB AUTO_INCREMENT=5001 DEFAULT CHARSET=utf8;
[/SQL]

De query is als volgt:

[SQL]
SELECT
LOCATIONS.LID,
LOCATIONS.NAME,
LOCATIONS.CITY,
COUNT(EVENTS.EID)
AS CNT,
IF( DATEDIFF(MAX(EVENTS.EVENTDATE), NOW() ) > 0, DATEDIFF(MAX(EVENTS.EVENTDATE), NOW()), -1)
AS PLANNING_SPAN_DAYS,
DATE_FORMAT(MAX(EVENTS.EVENTDATE), "%d-%m-'%y")
AS PLANNING_LATEST,
IF(DATEDIFF(NOW(), LATEST_CHECK) < 29, DATEDIFF(NOW(), LATEST_CHECK), 29)
AS LATEST_CHECK_DAYS,
DATE_FORMAT(LATEST_CHECK, "%d-%m-'%y")
AS LATEST_CHECK_DATE
FROM
LOCATIONS
LEFT JOIN
EVENTS
ON LOCATIONS.LID = EVENTS.LID
WHERE
LOCATIONS.ACTIVE = 1
GROUP BY
LOCATIONS.LID
ORDER BY
PLANNING_SPAN_DAYS ASC
[/SQL]

Dingen die mij opvallen zijn bijvoorbeeld dat in de query twee keer MAX(EVENTS.EVENTDATE) gebruikt wordt, ik weet niet of het wellicht mogelijk is om die 1 keer uit te rekenen en als variabele binnen de query te hergebruiken? Wellicht dat de primary key op een ander veld moet staan?

Alle hulp is welkom om de query-tijd zover mogelijk naar beneden te krijgen. Thanks alvast!

 

[ErikBooy007]

Hmm, ik weet niet wat er nu precies anders is, maar ik heb de query opnieuw opgebouwd:

[SQL]
SELECT
LOCATIONS.LID,
LOCATIONS.NAME,
LOCATIONS.CITY,
COUNT(EVENTS.EID)
AS CNT,
MAX(EVENTS.EVENTDATE)
AS MAX_DATE,
DATE_FORMAT(MAX(EVENTS.EVENTDATE), '%d-%m-\'%y')
AS LAST_EVENT_DATE,
DATE_FORMAT(LOCATIONS.LATEST_CHECK, '%d-%m-\'%y')
AS LAST_CHECK_DATE,
IF( DATEDIFF(MAX(EVENTS.EVENTDATE), DATE(NOW())) > 0, DATEDIFF(MAX(EVENTS.EVENTDATE), DATE(NOW())), -1 )
AS PLANNING_SPAN_DAYS,
IF( DATEDIFF(DATE(NOW()),LOCATIONS.LATEST_CHECK) <= 28, DATEDIFF(DATE(NOW()),LOCATIONS.LATEST_CHECK), -29 )
AS PLANNING_LAST_CHECK_DAYS
FROM
EVENTS
LEFT JOIN
LOCATIONS
ON LOCATIONS.LID = EVENTS.LID
WHERE
LOCATIONS.ACTIVE = 1
GROUP BY
EVENTS.LID
ORDER BY
MAX_DATE ASC
[/SQL]

En de execution time is nu nog gemiddeld 0,040 seconden. Ik heb wel een PRIMARY KEY toegevoegd op EID én LID in de EVENTS tabel, dus wellicht dat dat het verschil heeft gemaakt?

 

[ErikBooy007]

Het is toch nog niet helemaal opgelost. Door de aangepast query werden alleen nog locaties geselecteerd waarbij minimaal 1 evenement in de database stond, echter moeten ook locaties waarbij geen evenement bestaan geselecteerd worden. Als ik de JOIN omdraai (een RIGHT JOIN in bovenstaande query) of een selectie uit LOCATIONS met een JOIN van EVENTS dan is de query weer traag als vanouds.

Suggesties?

 

[ErikBooy007]

Nog een update, sorry. Na het toevoegen van een Foreign Key lijk ik weer wel alle data snel binnen te krijgen. De query duurt nu gemiddeld 0.035 seconden, wat voor dit doeleind compleet acceptabel is.

Suggesties over dingen die ik helemaal verkeerd doe zijn nog steeds welkom...