[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

[Conny Westh]

Jag gjorde en rolig variant där jag la upp en extern tabell med de genre man vill visa:

delimiter $$

CREATE TABLE `genreparam` (
  `idgenreparam` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idgenreparam`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Och så moddade jag SPn lite genom att lägga till ytterligare två subquerys för att hämta parametervärdena. La upp värdena 10 och 11 i parametertabellen, då returneras två rader från databasen, film 159 och 206. Exekveringstiden blir 16 ms varje gång jag kör.

delimiter $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getMoviesShowAllGenres2`()
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 (select idgenreparam from genreparam) GROUP BY m.id HAVING COUNT(DISTINCT mg.genre_id) = (select count(*) from genreparam) 
	) 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$$

[tartareandesire]

Man bör snarare fråga sig om man kör en webbplats där det görs många databasanrop och planera för den trafik man väntar sig nu och i framtiden. Att akutoptimera i efterhand är sällan en bra idé. Att göra självklara optimeringar som att undvika subqueries bör vara en naturlig del i utvecklingsarbetet för de flesta.

[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.