LIMIT on a WHERE condition, Advanced MYSQL
Hieronder een voorbeeld zoals mijn database er ongeveer uitziet:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
id | radio | datetime | artiest
---------------------------------------------
1 | 3fm | YYYY-MM-DD HH:MM:SS | madonna
2 | 3fm | YYYY-MM-DD HH:MM:SS | pink
3 | 538 | YYYY-MM-DD HH:MM:SS | anouk
4 | 538 | YYYY-MM-DD HH:MM:SS | tiesto
5 | 538 | YYYY-MM-DD HH:MM:SS | keane
6 | slam | YYYY-MM-DD HH:MM:SS | paul young
7 | slam | YYYY-MM-DD HH:MM:SS | milow
8 | 3fm | YYYY-MM-DD HH:MM:SS | fragma
9 | slam | YYYY-MM-DD HH:MM:SS | kesha
10 | slam | YYYY-MM-DD HH:MM:SS | bertolf
11 | 3fm | YYYY-MM-DD HH:MM:SS | jason mraz
---------------------------------------------
1 | 3fm | YYYY-MM-DD HH:MM:SS | madonna
2 | 3fm | YYYY-MM-DD HH:MM:SS | pink
3 | 538 | YYYY-MM-DD HH:MM:SS | anouk
4 | 538 | YYYY-MM-DD HH:MM:SS | tiesto
5 | 538 | YYYY-MM-DD HH:MM:SS | keane
6 | slam | YYYY-MM-DD HH:MM:SS | paul young
7 | slam | YYYY-MM-DD HH:MM:SS | milow
8 | 3fm | YYYY-MM-DD HH:MM:SS | fragma
9 | slam | YYYY-MM-DD HH:MM:SS | kesha
10 | slam | YYYY-MM-DD HH:MM:SS | bertolf
11 | 3fm | YYYY-MM-DD HH:MM:SS | jason mraz
Wat ik op dit moment gebruik ik een mysql query die de laatste 200 songs uit de database plukt en vervolgens hopen dat er van elk radiostation 13 bij zitten. Het komt helaas regelmatig voor dat er voor dat radiostation geen songs zijn toegevoegd in de laatste 200. Op die manier heb je dus geen laatste 13 songs die je kunt laten zien.
Ik ben op zoek gegaan naar een andere oplossing. Als je een LIMIT op een WHERE condition kan zetten zou dat een oplossing kunnen zijn. Dus dat je per radiostation zegt: 'Pak de laatste 13 songs'. Als je 1 query per radiostation uitvoert duurt het totaal 6 seconden. Dat is veel te lang voor de bezoeker.
Nu heb ik een oplossing gevonden die een stuk sneller is:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
$sqlQuery = mysql_query("
SELECT GROUP_CONCAT(
DISTINCT CONCAT(
'(SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = \"',
`radioname`,
'\" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)')
SEPARATOR ' union ') AS `query`
FROM `songs`") or die(mysql_error);
$rowQuery = mysql_fetch_assoc($sqlQuery);
$sql = mysql_query($rowQuery['query']);
SELECT GROUP_CONCAT(
DISTINCT CONCAT(
'(SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = \"',
`radioname`,
'\" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)')
SEPARATOR ' union ') AS `query`
FROM `songs`") or die(mysql_error);
$rowQuery = mysql_fetch_assoc($sqlQuery);
$sql = mysql_query($rowQuery['query']);
De eerste mysql query geeft deze output:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
(SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = "3fm" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)
union (SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = "538" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)
union (SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = "qmusic" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)
union (SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = "slamfm" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)
union (SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = "veronica" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)
union (SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = "skyradio" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)
union (SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = "538" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)
union (SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = "qmusic" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)
union (SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = "slamfm" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)
union (SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = "veronica" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)
union (SELECT `id`, `radioname`, `artist`, `song`, `datetime` FROM `songs` WHERE `radioname` = "skyradio" AND `datetime` <= NOW() ORDER BY `datetime` DESC LIMIT 13)
Deze output wordt vervolgens uitgevoerd. Deze oplossing maakt dus gebruik van 2 mysql query's die na elkaar worden uitgevoerd. Helaas duurt het laden van deze data nog 2 seconden. Dit is al beter maar nog steeds niet optimaal.
Heeft iemand misschien een betere oplossing dan hierboven is beschreven?
Mijn dank is groot,
Adriaan
Gewijzigd op 01/01/1970 01:00:00 door Adriaan
Quote:
Dit is de kern van het probleem en de belangrijkste oorzaak is een niet voldoende genormaliseerd datamodel en hoogstwaarschijnlijk het ontbreken van de nodige indexen in je tabel(len).Dus dat je per radiostation zegt: 'Pak de laatste 13 songs'. Als je 1 query per radiostation uitvoert duurt het totaal 6 seconden. Dat is veel te lang voor de bezoeker.
De gegevens die jij nu in de kolommen 'radio' en 'artiest' opslaat, hoort eigenlijk in aparte tabellen thuis. In de tabel die jij beschrijft, krijg je dan enkel nog de id's van de betreffende records uit de andere tabellen. Dit is de eerste winst aangezien een vergelijking met id's sneller is dan een vergelijking met tekst velden. Daarnaast zul je op een juiste manier indexen aan moeten brengen in je tabel. Als het (id van het) radiostation vaak in de WHERE voorkomt, loont het om op die kolom een index op te nemen. Lees ook eens deze tutorial over het gebruik van indexen...
Succes!
Blanche schreef op 17.12.2009 19:08:
De gegevens die jij nu in de kolommen 'radio' en 'artiest' opslaat, hoort eigenlijk in aparte tabellen thuis. In de tabel die jij beschrijft, krijg je dan enkel nog de id's van de betreffende records uit de andere tabellen. Dit is de eerste winst aangezien een vergelijking met id's sneller is dan een vergelijking met tekst velden.
Dat is niet persé waar, want stel dat je vervolgens weer de artiest wil tonen en daarvoor een join gaat maken met de artiest tabel wordt het weer een heel stuk trager.