Kom ihåg mig?
Home Menu

Menu


SQL hämta rader med vissa ord i

 
 
Ämnesverktyg Visningsalternativ
Oläst 2013-06-23, 23:44 #1
Anaxa Anaxa är inte uppkopplad
Medlem
 
Reg.datum: Jun 2013
Inlägg: 58
Anaxa Anaxa är inte uppkopplad
Medlem
 
Reg.datum: Jun 2013
Inlägg: 58
http://www.anaxa.se/sql.sql (kolumnen 'genres' i 'movies' skall tas bort. Han inte göra det innan jag exporterade)

Jag löste det jag vill ha hjälp med och det var att hämta ALLA filmer vars genrer kan vara både Action och Skräck. Alltså inte filmer som bara ha action och bara har skräck utan som har båda två.
Detta funkar bra med denna query:
PHP-kod:
SELECT m.* FROM movies m JOIN movie_genres mg ON mg.movie_id m.id AND mg.genre_id IN (111GROUP BY m.id HAVING COUNT(DISTINCT mg.genre_id) = 2 ORDER BY m.date ASC 
Här representerar 1 och 11 genrens ID

Men nu vill jag också hämta ALLA genrer en film har och lägg till det på ett eget fält: Action,Skräck,Komedi (det behöver inte vara avgränsat med komma). Bara så att jag kan få ut datan så att jag kan bearbeta den med PHP. Jag provade med detta:
PHP-kod:
SELECT m.*, GROUP_CONCAT(genres.name) AS genre_field FROM movies m JOIN movie_genres mg ON mg.movie_id m.id AND mg.genre_id IN (1,11JOIN genres ON mg.genre_id genres.id GROUP BY m.id HAVING COUNT(DISTINCT mg.genre_id) = 2 ORDER BY m.date ASC LIMIT 030 
Men ovanstående ger mig bara en rad med ett fält som heter genre_field med värdet 'Action,Skräck'. Värdet ska egentligen bli 'Action,Skräck,Fantasy'(alla genrer).

Filmen i fråga är Hansel och Gretel som ligger i movies-tabellen.
Anaxa är inte uppkopplad   Svara med citatSvara med citat
Oläst 2013-06-24, 00:05 #2
yakuzaemmes avatar
yakuzaemme yakuzaemme är inte uppkopplad
Mycket flitig postare
 
Reg.datum: Jun 2012
Inlägg: 773
yakuzaemme yakuzaemme är inte uppkopplad
Mycket flitig postare
yakuzaemmes avatar
 
Reg.datum: Jun 2012
Inlägg: 773
Citat:
Ursprungligen postat av Anaxa Visa inlägg
Jag löste det jag vill ha hjälp med
"Jag"
yakuzaemme är inte uppkopplad   Svara med citatSvara med citat
Oläst 2013-06-24, 00:49 #3
Conny Westh Conny Westh är inte uppkopplad
Klarade millennium-buggen
 
Reg.datum: Aug 2005
Inlägg: 5 166
Conny Westh Conny Westh är inte uppkopplad
Klarade millennium-buggen
 
Reg.datum: Aug 2005
Inlägg: 5 166
Citat:
Ursprungligen postat av Anaxa Visa inlägg
Men nu vill jag också hämta ALLA genrer en film har och lägg till det på ett eget fält: Action,Skräck,Komedi (det behöver inte vara avgränsat med komma). Bara så att jag kan få ut datan så att jag kan bearbeta den med PHP. Jag provade med detta:
.
.
.
Citat:
Ursprungligen postat av Anaxa Visa inlägg
Men ovanstående ger mig bara en rad med ett fält som heter genre_field med värdet 'Action,Skräck'. Värdet ska egentligen bli 'Action,Skräck,Fantasy'(alla genrer).

Filmen i fråga är Hansel och Gretel som ligger i movies-tabellen.
Blir bättre om du använder CODE och /CODE -taggarna i stället för PHP /PHP....

Är det så här du menar?

Kod:
use anaxa;

SELECT m.*, GROUP_CONCAT(genres.name) AS genre_field 
FROM movies m 
JOIN movie_genres mg ON mg.movie_id = m.id AND mg.genre_id IN (select id from genres) 
JOIN genres ON mg.genre_id = genres.id 
GROUP BY m.id 
HAVING COUNT(DISTINCT mg.genre_id) = 2 
ORDER BY m.date ASC LIMIT 0, 30;

Senast redigerad av Conny Westh den 2013-06-24 klockan 00:58
Conny Westh är inte uppkopplad   Svara med citatSvara med citat
Oläst 2013-06-23, 15:05 #4
jonny jonny är inte uppkopplad
Supermoderator
 
Reg.datum: Sep 2003
Inlägg: 6 941
jonny jonny är inte uppkopplad
Supermoderator
 
Reg.datum: Sep 2003
Inlägg: 6 941
TESTA:
PHP-kod:
SELECT 
FROM movies 
JOIN movie_genres ON movie_genres
.movie_id movies.id 
WHERE movie_genres
.genre_id IN (1,2)
LIMIT 030
jonny är inte uppkopplad   Svara med citatSvara med citat
Oläst 2013-06-23, 18:17 #5
Anaxa Anaxa är inte uppkopplad
Medlem
 
Reg.datum: Jun 2013
Inlägg: 58
Anaxa Anaxa är inte uppkopplad
Medlem
 
Reg.datum: Jun 2013
Inlägg: 58
Citat:
Ursprungligen postat av jonny Visa inlägg
TESTA:
PHP-kod:
SELECT 
FROM movies 
JOIN movie_genres ON movie_genres
.movie_id movies.id 
WHERE movie_genres
.genre_id IN (1,2)
LIMIT 030
Den ger mig alla rader vad jag än stoppar in i IN()
PHP-kod:
SELECT 
FROM movies 
JOIN movie_genres ON movie_genres
.movie_id movies.id 
WHERE movie_genres
.genre_id IN (1,2,3,4
Anaxa är inte uppkopplad   Svara med citatSvara med citat
Oläst 2013-06-24, 08:38 #6
lubic lubic är inte uppkopplad
Medlem
 
Reg.datum: Aug 2005
Inlägg: 205
lubic lubic är inte uppkopplad
Medlem
 
Reg.datum: Aug 2005
Inlägg: 205
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å.
lubic är inte uppkopplad   Svara med citatSvara med citat
Oläst 2013-06-24, 09:18 #7
starstable starstable är inte uppkopplad
Nykomling
 
Reg.datum: Feb 2013
Inlägg: 23
starstable starstable är inte uppkopplad
Nykomling
 
Reg.datum: Feb 2013
Inlägg: 23
Citat:
Ursprungligen postat av lubic Visa inlägg
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
starstable är inte uppkopplad   Svara med citatSvara med citat
Oläst 2013-06-24, 17:01 #8
Conny Westh Conny Westh är inte uppkopplad
Klarade millennium-buggen
 
Reg.datum: Aug 2005
Inlägg: 5 166
Conny Westh Conny Westh är inte uppkopplad
Klarade millennium-buggen
 
Reg.datum: Aug 2005
Inlägg: 5 166
Citat:
Ursprungligen postat av starstable Visa inlägg
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....

Kod:
-- --------------------------------------------------------------------------------
-- 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

Senast redigerad av Conny Westh den 2013-06-24 klockan 17:13
Conny Westh är inte uppkopplad   Svara med citatSvara med citat
Oläst 2013-06-24, 19:01 #9
Clarence Clarence är inte uppkopplad
Administratör
 
Reg.datum: Jan 2003
Inlägg: 1 974
Clarence Clarence är inte uppkopplad
Administratör
 
Reg.datum: Jan 2003
Inlägg: 1 974
Citat:
Ursprungligen postat av ConnyWesth Visa inlägg
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.
Clarence är inte uppkopplad   Svara med citatSvara med citat
Oläst 2013-06-24, 11:28 #10
Anaxa Anaxa är inte uppkopplad
Medlem
 
Reg.datum: Jun 2013
Inlägg: 58
Anaxa Anaxa är inte uppkopplad
Medlem
 
Reg.datum: Jun 2013
Inlägg: 58
Citat:
Ursprungligen postat av lubic Visa inlägg
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
Anaxa är inte uppkopplad   Svara med citatSvara med citat
Svara


Aktiva användare som för närvarande tittar på det här ämnet: 1 (0 medlemmar och 1 gäster)
 

Regler för att posta
Du får inte posta nya ämnen
Du får inte posta svar
Du får inte posta bifogade filer
Du får inte redigera dina inlägg

BB-kod är
Smilies är
[IMG]-kod är
HTML-kod är av

Forumhopp


Alla tider är GMT +2. Klockan är nu 20:38.

Programvara från: vBulletin® Version 3.8.2
Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Svensk översättning av: Anders Pettersson
 
Copyright © 2017