SQL hämta rader med vissa ord i
 

Jag har flera rader videos i en rad och varje rad har en kolumn som heter genres.

kolumnen kan innehålla flera genres som är avgränsade. Det kan också innehålla endast 1 genre. Lat som jag är har jag inte strippat den sista '|' men jag hoppas på att det kommer lösas ändå. Såhär kan kolumnen se ut:

musical|
family|comedy|
horror|comedy|action|
action|adventure|comedy|

Om jag nu vill hämta alla rader där comedy finns med, hur ska jag då gå tillväga för att lösa detta? Måste jag splitta kolumnen i flera bitar först?

Hej Anaxa!
Om du behöver få ut rader från kolumnen genre som innehåller ordet "comedy" ska det här fungera bra för MySql.

SELECT * FROM videoTable WHERE genre LIKE '%comedy%'

Detta var ett sätt att lösa det på.

Lycka till!

Hmm det hade jag inte tänkt på alls faktiskt. Tack du ^_^

Varsågod och det är aldrig fel att fråga (efter att man har provat såklart ;)

Värt att notera är att en LIKE '%...%' kommer slöa ner din databas rejält när du får lite mer data. Alternativen är att strukturera din data bra (3NF) eller skapa ett sökindex i annan mjukvara. Ska tabellen inte växa ordentligt med tiden finns det dock ingen poäng att oroa sig (med många frågor så kommer query cachen ta hand om problemet).

Jag håller med Clarence. När du får lite storlek på din databas så bör du funderar på andra lösningar eller åtminstone lägga dit ett index på genre med en längd på kanske 15 char.

Du kan också skapa en separat tabell där filmen finns med flera gånger, en gång per kategori (en->många). Men JOINS är inte heller alltid så snabba men det vi pratar om här att optimera sina sql-frågor blir ju lite överkurs om Anaxas databas innehåller litet antal poster.

2 tabeller
Alltså movies.Genres kan innehålla action|comedy|family och det är dessa 3 som pekar mot genres.Machine_Name

Vet inte hur jag kan förbättra sånt här faktiskt. Jag har tänkt en hel del men jag fastnade som sagt vid sökningen efter genre(vilket jag löst nu tack vare corneliisandberg)

EDIT:
En idé jag har är att skapa en tredje tabell med 3 kolumner (id,movie_id, genre_id). Så för varje genre en film har så lägger jag bara till 1 rad? Är osäker på om det är snabbare eller inte. Det får ni som kan det avgöra ^_^

Tack Anaxa för ditt exempel. Nu blir det lättare att se vart du fastnar vid snabb skummning.

Kolumnen "Genres" i tabellen movies är överflödig på det viset jag tänker mig. Finns många sätt men jag ger dig ett alternativ.


// movies
ID PRIMARY_KEY
Name
Date_Added
Director
URL
Likes
Views

// genres
ID PRIMARY_KEY
MovieId (detta ger kopplingen till själva filmen ovan)
Name
Machine_Name (OT:?)

Movies låtsas vi innehåller detta
1, Det våras för Bamse, 12345678, George Lucas, www.bamse.se,12,5
2, Gökboet, 12345678, Steven S, www.gok.se,8,6
3,Excorsisten,12245987,Stephen Best,www.exco.se,34,4

Genres innehåller detta
1,1,Cartoon,?
2,1,Barnaction,?
3,1,Icke våld,?
4,2,Thriller,?
5,2,Ondska,?
6,3,Ondska,?

För att hämta alla filmer som är "Cartoon" skriv följande fråga
SELECT * FROM movies
JOIN genres
ON genres.MovieId = movies.ID
WHERE genres.Name = 'Cartoon'

För att hämta alla filmer som är "Ondska"
SELECT * FROM movies
JOIN genres
ON genres.MovieId = movies.ID
WHERE genres.Name = 'Ondska'

Du ser nu att du alltså kan addera flera filmtitlar via filmens id i tabellen genres via kolumnen MoviesId som vi har där.

Som alltid måste alla kolumner och frågor vara rättstavade och konsekventa, något jag frisvär mig ifrån sittandes på jobbet som jag är ;)

Men jag hoppas du förstår poängen och vart du fastnade lite i tankesättet.

Lycka till och berätta gärna hur det går för dig!

Jag skulle ha gjort såhär:
Sen för att visa filmer för en viss genre om du har urlen http://dinsida.se/genre?id=2:
Skriver aldrig raw sql längre så har ej testat koden, men något liknande i alla fall.

Det skulle isåfall bli ett index som inte används alls för frågan ... Ett index är sorterat, med %..% kan du inte söka sorterat.

Till TS: Strukturen som ges av föregående skribenter är vad som kallas normalisering. Det gör det mycket flexiblare att ställa frågor, uppdatera delar av datan samt att det ger dig bättre förutsättningar att skriva effektiva queries (%..% funkar absolut bara i liten skala, t ex).

Byggde något likt IMDB för några års sedan och kan meddela herrn att ett 10-tal tabeller till behövs för att få plats med all information.

Just nu har du te.x en director direkt i film-tabellen när en film kan ha flera directors. Ett tips är även att normalisera upp till och med 4NF. På så vis minimerar du duplicering av data.

Nerix, jag förstår inte riktigt varför det behövs här?
Slängde ihop en sak som visar vad jag har i min databas:
http://www.anaxa.se/schema.php

Kan jag verkligen göra det enklare? Det känns bara krångligare att göra fler tabeller?

Det beror ju på vad du menar med enklare. Om du ska kunna göra systemet skalbart och göra effektiva sökningar så bör du normalisera din data. Är det 100% säkert att det inte växer nämnvärt så är det inte lika viktigt. Du verkar ha lite problem med din data förresten med negativa timestamps på några ställen.

Databasen kommer växa ;) men jag vill försöka optimera allting nu innan det drar igång.

Det där med negativa timestamps är inget fel utan jag sparar bara ett datum bakom 1970

Läs på om normalisering så förstår du säker vad han menar.

Aj då, sorry, missade det :) Sitter på mobilen så allt är lite mindre överskådligt. Hur som helst så bör du strukturera din databas annorlunda så att du slipper trycka in flera värden i samma fält. Du får då snabbt problem. Tänk också på att två personer kan ha samma namn ifall det blir relevant.

Nu som ni kan se på länken så har jag droppat fältet 'genres' på ´movies´-tabellen och lagt in en tabell som heter 'movies_genres'. Den tabellen har relation med tabellerna 'movies' och 'genres'. Det borde följa 3nf om jag har läst på rätt.

@tartareandesire | jo jag antar att jag får lägga alla regissörer i en tabell också på samma sätt som jag gjort med genres.

OT: Om det nu är så att två regissörer har likadana namn, finns det då något sätt att urskilja dem? Jag använder http://www.omdbapi.com/ för att få informationen om en specifik film. Men den hämtar namn och inget mer. Jag vet utifrån att ha kollat på Imdb att varje regissör har ett unikt id så jag antar att jag får göra en kolumn för en regissörs id i min databas.(guh va jobbigt :P)

Det mest grundläggande inom normalisering är att varje kolumn ska innehålla atomära värden, dvs du lägger ALDRIG ihop flera värden i samma kolumn. Det är 1NF (första normalformen).

3NF innebär att varje atomärt värde ska vara beroende av hela primärnyckeln och inget annat än primärnyckeln, ... so help me Codd som man brukar säga... (Codd var en matematiker som definierade normaliseringen i relationsdatabaser).

Okej conny, så om alla filmer enligt min tabell har likadant årtal i sitt 'year' fält så strider det emot tredje formens normalisering?

Jag funderade också hur på jag ska hämta alla filmer som har genrerna komedi och skräck.

Jag provade: vilket inte jag mig någonting eftersom samma värde kan inte vara 2 olika saker samtidigt. Men detta är det bästa jag kan komma på.

TESTA:

Den ger mig alla rader vad jag än stoppar in i IN()

Kan du lägga upp DDL-scriptet och ett script med INSERT av data i alla tabellerna så underlättar det om du vill ha hjälp, så slipper jag sitta och knappa in detta manuellt....

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

"Jag" :rolleyes:

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

Inte riktigt.

Säg att en film har 5 olika genrer, Äventyr, Action, Komedi, Skräck och Sport. Vi kan även anta att det finns flera filmer som har liknande genrer.

Nu vill jag filtrera filmerna för att få fram alla de filmer som har både Action och Skräck som genrer. Detta löser jag igenom att köra:
Här hämtar jag data enbart ifrån 'movies'. So far so good.
Men nu kommer vi till de knepiga och själva problemet.
Jag vill samtidigt hämta alla genrer som hör till en viss film. Detta gör jag för informationssyfte. Liksom att bara visa vilka genrer en specifik film har.

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

Nu tog jag iofs koden med HAVING från kod ovan. Det jag gjorde var att joina på lite tabeller för att hämta ut mer information. Men som sagt, det är kanske inte omöjligt att join på detta på en gång, för att slippa subqueries? Vet dock inte om det är någon märkbar skillnad i prestanda att göra så?

Men du får gärna ge en query som fungerar för att ta fram alla filmer som har två (eller flera) genrer, som då alltså inte använder HAVING på detta sätt. Om det nu skulle ge bättre prestanda, dvs.

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

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

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

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.

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.

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.