[MySQL] tellen hoeveel keer een record vookomt

[ieperlingetje]

Ik bewaar alle lijsten van de iTunes Top 100, en ik wil bepalen of een single voor de eerste maal voorkomt in de iTunes Top 100 van dat land. Alleen krijg ik het met een subquery niet meteen voor elkaar, omdat deze steeds wordt toegepast in de WHERE clausule, terwijl ik het aantal gewoon als extra kolom wil weergeven.

Tabelstructuur:


tblTop100Lijsten

LijstID	Land	Datum
1	US	10/03/2011
2	US	11/03/2011
3	US	12/03/2011

tblTop100Lijst

LijstID	nr	songID
1	1	1
1	2	2
2	1	1
2	2	2
3	1	2
3	2	1
3	3	3

tblTop100Songs

songID	Titel	Artiest
1	Titel 1	Artiest 1
2	Titel 2	Artiest 2
3	Titel 3	Artiest 3

Wat ik dus als view wil bekomen bij lijstID 3:

nr	Titel	Artiest	#
1	Titel 1	Artiest 1	3
2	Titel 2	Artiest 2	3
3	Titel 3	Artiest 3	1

In bovenstaande voorbeeld zou dus bij de iTunes Lijst op 12 maart songID 3 voor de eerste maal voorkomen.


Ik heb al op de mysql site wat zitten uitzoeken, maar kom er maar niet uit (http://dev.mysql.com/tech...es/subqueries_part_1.html)

[Jaap-Jan]

Programming FAQ - SQL

[ACM]

Je kan ook een subselect in de select-list zelf stoppen

Dus bijvoorbeeld zoiets doen:

SQL:

SELECT songid, (SELECT COUNT(*) FROM tblTop100Lijst l WHERE l.songid = s.songid)
FROM tblTop100Songs s 
-- etc

Maar volgens mij kan je jouw beoogde effect ook gewoon zonder subqueries oplossen met een group by

SQL:

SELECT s.songid --, ...
COUN(l.songid) as nr
FROM tblTop100Songs s 
LEFT JOIN tblTop100Lijst l ON s.songid = l.songid
GROUP BY s.songid -- , ...

En Jaap-Jan heeft idd gelijk dat dat in de SQL-faq genoemd wordt (als je weet hoe het concept heet waar je op zoekt).

[ Voor 10% gewijzigd door ACM op 10-03-2011 16:00 ]

[ieperlingetje]

Bedankt ACM voor de voorbeeldquery. Is de performance van count bij 20000 records echt zo laag dat dat 5 seconden duurt?

[Megamind]

Misschien even verdiepen in indexen

[Verwijderd]

Dat ligt voor een groot deel aan je indexes. Bij MySQL verwacht ik dat de tweede suggestie van ACM een stuk sneller is dan de eerste.

[ieperlingetje]

GROUP BY kan ik niet gebruiken, omdat het aantal afhangt van een voorwaarde die ik moet meegeven. Ik heb de query ondertussen al het juiste resultaat kunnen opleveren, alleen duurt die 147 seconden (!) bij een tabel van 200 000 records om uit te voeren:

SQL:

SELECT nr, Titel, Artiest, tblTop100Songs.songID AS sID, tblTop100Lijsten.LijstID AS Lijst, Datum, (
SELECT MIN(tblTop100Lijsten.LijstID) 
FROM tblTop100Lijsten
INNER JOIN tblTop100Lijst ON (tblTop100Lijstt.LijstID = tblTop100Lijsten.LijstID ) 
WHERE tblTop100Lijst.songID = sID
AND Land =  'BE') AS MinLijstID
FROM iTunesTop_songs
INNER JOIN tblTop100Lijst ON ( tblTop100Lijst.songID = tblTop100Songs.songID ) 
INNER JOIN tblTop100Lijsten ON (tblTop100Lijst.LijstID = tblTop100Lijsten.LijstID) 
WHERE Land =  'BE'
ORDER BY Lijst DESC , nr ASC 
LIMIT 0 , 10

Hierbij krijg ik dus het LijstID van wanneer de song voor het eerst werd toegevoegd aan de lijst, afhankelijk van het land van de lijst. Kan ik deze query nog optimaliseren? Mijn Indexen zijn overal goed ingesteld op de primary keys.

[GlowMouse]

Dit soort informatie wordt 1x ingevoerd en daarna heel vaak geraadpleegd. Ik zou denormaliseren.

Om die query te optimaliseren, heb ik op zijn minst de output van EXPLAIN en een overzichtje van je tabellen plus indices nodig.

[ieperlingetje]

Hier alle info, wat ik merk na explain is dat alle rijen worden opgevraagd ipv enkel diegene die aan de voorwaarde voldoen, 'k heb alleen geen idee hoe ik dat aanpas.

code:

EXPLAIN  iTunesTop_lijsten

Field:  Type:   Null:   Key:    Default:    Extra:
LijstID int(6)  NO  PRI     auto_increment
Land    varchar(2)  NO          
Datum   date    NO          


EXPLAIN  iTunesTop_lijst

Field:  Type:   Null:   Key:    Default:    Extra:
LijstID int(6)  NO  PRI     
nr  int(3)  NO  PRI     
songID  int(8)  NO  

EXPLAIN  iTunesTop_songs

Field:  Type:   Null:   Key:    Default:    Extra:
songID  int(8)  NO  PRI     auto_increment
Titel   varchar(255)    NO          
Artiest varchar(50) NO          
ytID    varchar(15) NO          

EXPLAIN SELECT nr, Titel, Artiest, ytID, iTunesTop_songs.songID AS sID, iTunesTop_lijsten.LijstID AS Lijst, Datum, (
SELECT MIN(iTunesTop_lijsten.LijstID) 
FROM iTunesTop_lijsten
INNER JOIN iTunesTop_lijst ON ( iTunesTop_lijst.LijstID = iTunesTop_lijsten.LijstID ) 
WHERE iTunesTop_lijst.songID = sID
AND Land =  'BE') AS MinLijstID
FROM iTunesTop_songs
INNER JOIN iTunesTop_lijst ON ( iTunesTop_lijst.songID = iTunesTop_songs.songID ) 
INNER JOIN iTunesTop_lijsten ON ( iTunesTop_lijst.LijstID = iTunesTop_lijsten.LijstID ) 
WHERE Land =  'BE'
ORDER BY Lijst DESC , nr ASC 
LIMIT 0 , 10

d:  select_type:    table:  type:   possible_keys:  key:    key_len:    ref:    rows:   Extra:
1   PRIMARY iTunesTop_lijst ALL PRIMARY             22000   Using temporary; Using filesort
1   PRIMARY iTunesTop_songs eq_ref  PRIMARY PRIMARY 4   randomize_be.iTunesTop_lijst.songID 1   
1   PRIMARY iTunesTop_lijsten   eq_ref  PRIMARY PRIMARY 4   randomize_be.iTunesTop_lijst.LijstID    1   Using where
2   DEPENDENT SUBQUERY  iTunesTop_lijst ALL PRIMARY             22000   Using where
2   DEPENDENT SUBQUERY  iTunesTop_lijsten   eq_ref  PRIMARY PRIMARY 4   randomize_be.iTunesTop_lijst.LijstID    1   Using where

iTunesTop_lijsten

LijstID	Land	Datum
1	US	10/03/2011
2	US	11/03/2011
3	US	12/03/2011
4	BE	12/03/2011

PK: LijstID

iTunesTop_lijst

LijstID	nr	songID
1	1	1
1	2	2
2	1	1
2	2	2
3	1	2
3	2	1
3	3	3
4	1	2
4	2	1

PK: compound van LijstID en nr

iTunesTop_songs

songID	Titel	Artiest
1	Titel 1	Artiest 1
2	Titel 2	Artiest 2
3	Titel 3	Artiest 3

PK: songID

[ Voor 0% gewijzigd door RobIII op 12-03-2011 17:41 ]

[RobIII]

ieperlingetje schreef op zaterdag 12 maart 2011 @ 17:16:
wat ik merk na explain is dat alle rijen worden opgevraagd ipv enkel diegene die aan de voorwaarde voldoen, 'k heb alleen geen idee hoe ik dat aanpas.

Door je te verdiepen in de materie; een RDBMS is meer dan een "flikker hier je records maar in"
En ga nou niet als een dolle op elk veld een index mikken

[ Voor 14% gewijzigd door RobIII op 12-03-2011 17:41 ]

[GlowMouse]

Zoals ik al zei, denormaliseren. Dan heb je dit over:

SQL:

SELECT nr, Titel, Artiest, ytID, iTunesTop_songs.songID AS sID, iTunesTop_lijsten.LijstID AS Lijst, Datum, iTunesTop_lijst.MinLijstID
FROM iTunesTop_lijst
INNER JOIN iTunesTop_songs ON ( iTunesTop_lijst.songID = iTunesTop_songs.songID )
INNER JOIN iTunesTop_lijsten ON ( iTunesTop_lijst.LijstID = iTunesTop_lijsten.LijstID )
WHERE Land = 'BE'
ORDER BY Lijst DESC , nr ASC
LIMIT 0 , 10

Je doet LIMIT 0,10 terwijl er 100 nummers in één Top100 staan. Is ORDER BY Lijst DESC dan echt nodig, of wil je gewoon de laatste lijst hebben? _{je neemt nu wel aan dat lijsten in chronologische volgorde worden toegevoegd} Als je alleen de laatste lijst wilt hebben, levert dat een veel snellere query op:

SQL:

INNER JOIN iTunesTop_lijst ON ( iTunesTop_lijst.LijstID = iTunesTop_lijsten.LijstID )
WHERE iTunesTop_lijst.songID = sID
AND Land = 'BE') AS MinLijstID
FROM iTunesTop_songs
INNER JOIN iTunesTop_lijst ON ( iTunesTop_lijst.songID = iTunesTop_songs.songID )
INNER JOIN iTunesTop_lijsten ON ( iTunesTop_lijst.LijstID = iTunesTop_lijsten.LijstID )
WHERE iTunesTop_lijsten.LijstID = (SELECT MAX(LijstID) FROM iTunesTop_lijsten WHERE Land='BE')
ORDER BY nr ASC
LIMIT 0 , 10

Je kunt nog een samengestelde index zetten op (Land,LijstID) om deze sneller te maken.

[RobIII]

GlowMouse schreef op zaterdag 12 maart 2011 @ 17:42:
Zoals ik al zei, denormaliseren

Ik zie zo snel de noodzaak tot denormaliseren (nog) niet

[GlowMouse]

RobIII schreef op zaterdag 12 maart 2011 @ 17:44:
[...]

Ik zie zo snel de noodzaak tot denormaliseren (nog) niet

Momenteel filtert en sorteert hij zijn resultset pas nadat deze subquery wordt uitgevoerd:

SQL:

SELECT MIN(iTunesTop_lijsten.LijstID) 
FROM iTunesTop_lijsten
INNER JOIN iTunesTop_lijst ON ( iTunesTop_lijst.LijstID = iTunesTop_lijsten.LijstID ) 
WHERE iTunesTop_lijst.songID = 12345
AND Land =  'BE')

Die wordt dus voor elk liedje uitgevoerd (of meerdere keren per liedje, bij te kleine join cache). Na mijn optimalisatie wordt hij nog maar 10x uitgevoerd. De noodzaak is dus kleiner geworden. Maar om deze query uit te voeren, moet hij voor een liedje dat in 50 landen gedurende 10 weken in de Top100 staat, 500 rijen afgaan. Beetje zonde voor data die niet wijzigt.

[ieperlingetje]

Bedankt GlowMouse, de tijd is gereduceerd tot een halve seconde

En RobIII, wat is er mis met de keuze voor mijn indexen? (nu ik erover nadenk, bedoel je de compound key ?)

[GlowMouse]

Een halve seconde vind ik nog lang.ah, niet gedenormaliseerd.

Heb je nieuwe EXPLAIN-output?

[ Voor 21% gewijzigd door GlowMouse op 12-03-2011 20:43 ]

[ieperlingetje]

id	select_type	table	type	possible_keys	key	key_len	ref	rows
1	PRIMARY	iTunesTop_lijsten	const	PRIMARY	PRIMARY	4	const	1
1	PRIMARY	iTunesTop_lijst	ref	PRIMARY	PRIMARY	4	const	88	Using where
1	PRIMARY	iTunesTop_songs	eq_ref	PRIMARY	PRIMARY	4	randomize_be.iTunesTop_lijst.songID	1
3	SUBQUERY	iTunesTop_lijsten	ALL	NULL	NULL	NULL	NULL	220	Using where
2	DEPENDENT SUBQUERY	iTunesTop_lijst	ALL	PRIMARY	NULL	NULL	NULL	22000	Using where
2	DEPENDENT SUBQUERY	iTunesTop_lijsten	eq_ref	PRIMARY	PRIMARY	4	randomize_be.iTunesTop_lijst.LijstID	1	Using where

Overigens worden de resultaten van query's door MySQL gecacht, en omdat de lijsten toch niet veranderen draait alles snel

[ Voor 4% gewijzigd door ieperlingetje op 12-03-2011 20:03 ]