[lubic]

Nu har jag iofs inte testat, men något liknade detta borde gå att använda:

SELECT q1.id, q1.date, group_concat(genres.name) genre_field FROM
(
SELECT m.id, m.date FROM movies m JOIN movie_genres mg ON mg.movie_id = m.id AND mg.genre_id IN (1, 11) GROUP BY m.id HAVING COUNT(DISTINCT mg.genre_id) = 2
) q1
JOIN movie_genres mg on mg.movie_id = q1.id
JOIN genres ON mg.genre_id = genres.id
GROUP BY q1.id
ORDER BY q1.date ASC

Det går kanske att joina på denna information redan i första frågan, vilket kanske är mer effektivt? Man får helt enkelt testa hur pass bra denna fråga är rent prestandamässigt för att se om det är en möjlig väg att gå.

[starstable]

Ursprungligen postat av lubic

Nu har jag iofs inte testat, men något liknade detta borde gå att använda:

SELECT q1.id, q1.date, group_concat(genres.name) genre_field FROM
(
SELECT m.id, m.date FROM movies m JOIN movie_genres mg ON mg.movie_id = m.id AND mg.genre_id IN (1, 11) GROUP BY m.id HAVING COUNT(DISTINCT mg.genre_id) = 2
) q1
JOIN movie_genres mg on mg.movie_id = q1.id
JOIN genres ON mg.genre_id = genres.id
GROUP BY q1.id
ORDER BY q1.date ASC

Det går kanske att joina på denna information redan i första frågan, vilket kanske är mer effektivt? Man får helt enkelt testa hur pass bra denna fråga är rent prestandamässigt för att se om det är en möjlig väg att gå.

Den querien du just skrev, är inte optimerad alls, du använder having, vilket innebär att du hämtar ut ALLA rader.

Skriv om, men gör rätt Skippa Subqueries till så många delar du kan

[Conny Westh]

Ursprungligen postat av starstable

Den querien du just skrev, är inte optimerad alls, du använder having, vilket innebär att du hämtar ut ALLA rader.

Skriv om, men gör rätt Skippa Subqueries till så många delar du kan

Den dynamiska SQL-frågan tog 31 ms att köra första gången för mig och 16 ms andra gången, så det är inte alls särskilt farligt. Jag kör bara InnoDB, dvs inga MyIsam-tabeller.

Jag gjorde om den till en SP och då droppade exekveringstiden till 16 ms. Jag testade även att lägga till genre 10 för att testa med 3 och det var exakt samma exekveringstid på 16 ms. Jag kör ett gammalt tröskverk som Lenovo Thinkpad T410 på 2,4 GHz och prestanda 4,2 enligt MinDator.System....

Noterat är att när jag körde USE på databasen så tog det också 15-16 ms.

Det är en överdriven rädsla at använda Subquerys för är inte alls så prestandakrävande som det påstås. Blir det en flaskhals får man väl ta det problemet då, men SQL-Engine gör sina egna optimeringar och i synnerhet om man gör om frågan till en SP.

Prestandamässigt vinner man oftast mer på att ha rätt index.

Men visst, jag förnekar inte tat en JOIN kan vara snabbare, men frågan är hur många år man måste köra applikationen innan optimeringsjobbet lönar sig....

Prestandaförlusten i det här fallet borde helt enkelt vara omärkbar för användaren. Om man optimerar frågan ner till 0 så kan man inte vinna mer än 16 ms .... men användaren kommer inte att märka någon skillnad ändå, för det tar längre tid att tanka ner webbsidan som visar resultatet....

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getMoviesShowAllGenres`()
BEGIN
	SELECT q1.id, q1.date, group_concat(genres.name) genre_field FROM
	(
		SELECT m.id, m.date FROM movies m JOIN movie_genres mg ON mg.movie_id = m.id AND mg.genre_id IN (1, 11) GROUP BY m.id HAVING COUNT(DISTINCT mg.genre_id) = 2 
	) q1
	JOIN movie_genres mg on mg.movie_id = q1.id
	JOIN genres ON mg.genre_id = genres.id
	GROUP BY q1.id
	ORDER BY q1.date ASC ;
END

[Clarence]

Ursprungligen postat av ConnyWesth

Den dynamiska SQL-frågan tog 31 ms att köra första gången för mig och 16 ms andra gången, så det är inte alls särskilt farligt. Jag kör bara InnoDB, dvs inga MyIsam-tabeller.

Jag gjorde om den till en SP och då droppade exekveringstiden till 16 ms. Jag testade även att lägga till genre 10 för att testa med 3 och det var exakt samma exekveringstid på 16 ms. Jag kör ett gammalt tröskverk som Lenovo Thinkpad T410 på 2,4 GHz och prestanda 4,2 enligt MinDator.System....

Noterat är att när jag körde USE på databasen så tog det också 15-16 ms.

Det är en överdriven rädsla at använda Subquerys för är inte alls så prestandakrävande som det påstås. Blir det en flaskhals får man väl ta det problemet då, men SQL-Engine gör sina egna optimeringar och i synnerhet om man gör om frågan till en SP.

Prestandamässigt vinner man oftast mer på att ha rätt index.
[/Code]

Vad är "rätt" index för din fråga? Jag kan iallafall inte luska ut hur du med MySQL ska lyckas få större delen av raderna frågan behöver från index ...

Det finns olika sätt att mäta prestandan. Men en väldigt bra fingervisning får du av att köra EXPLAIN på frågan. Ditt exempel skapar en ny temporär tabell av alla rader från movie_genres som den sedan sorterar utan index. (370 rows, Using where; Using temporary, Using filesort). Slå av query cache och lägg till ett par hundra tusen rader så ser du nog effekten av det direkt.

Ett par hundra rader läses nu, vad händer vid ett par miljoner rader och 100 besökare på samma gång?

Vill du göra det lätt för dig med subqueries kan det finnas poäng att prova på frågan med MariaDB. Det är en drop-in replacement för MySQL med många subquery optimeringar.

[Anaxa]

Ursprungligen postat av lubic

Nu har jag iofs inte testat, men något liknade detta borde gå att använda:

SELECT q1.id, q1.date, group_concat(genres.name) genre_field FROM
(
SELECT m.id, m.date FROM movies m JOIN movie_genres mg ON mg.movie_id = m.id AND mg.genre_id IN (1, 11) GROUP BY m.id HAVING COUNT(DISTINCT mg.genre_id) = 2
) q1
JOIN movie_genres mg on mg.movie_id = q1.id
JOIN genres ON mg.genre_id = genres.id
GROUP BY q1.id
ORDER BY q1.date ASC

Det går kanske att joina på denna information redan i första frågan, vilket kanske är mer effektivt? Man får helt enkelt testa hur pass bra denna fråga är rent prestandamässigt för att se om det är en möjlig väg att gå.

Det funkade bra Ska nog köra på denna tillsvidare