Get N rows of a group / thread / topic

I'm looking for a right mysql query for my website http://watiseropderadio.nl (in dutch). It's a website to find out what songs have been played on the popular radio's in Holland.

I want to make a query what returns 13 rows for each radiostation. There are 8 different radiostation.

This is my table:

id       | radioname       | time  | artist    | song
--------------------------------------------------------------
23421    | radio 538       | 19:34 | Queen     | Bohemian
23422    | radio veronica  | 19:35 | Rammstein | Blablabla
23423    | slam fm         | 19:34 | Roxette   | Blablabla
23424    | 3fm         开发者_运维技巧    | 19:34 | Blabla    | Blablabla
....

It's a table with 3,000,000+ records and with these key's:

PRIMARY             PRIMARY  3083007   id
radioname           INDEX    8         radioname
track with artist   INDEX    23715     artist
                                       song

This what I want to be the output:

id       | radioname       | time  | artist    | song
--------------------------------------------------------------
23455    | radio 538       | 19:30 | Blabla    | Blablabla
23470    | radio 538       | 19:33 | Blabla    | Blablabla
23484    | radio 538       | 19:36 | Blabla    | Blablabla
23498    | radio 538       | 19:38 | Blabla    | Blablabla
total 13 x ....
23456    | radio veronica  | 19:29 | Blabla    | Blablabla
23476    | radio veronica  | 19:32 | Blabla    | Blablabla
23483    | radio veronica  | 19:36 | Blabla    | Blablabla
23495    | radio veronica  | 19:39 | Blabla    | Blablabla
total 13 x ....

The order of the result doesn't matter and the query has to be fast. I like to try it myself, but this is a little to much for me.

I don't know if the key's are goed for this job, can someone gives some advise?

This is tricky because, while there are ways to say "get me only 13 records for this query", to the best of my knowledge there's no way to directly say "get me only 13 records for this each value of field x".

The only way I can think of to do it is to add an "play number" field to the record, or to create another table to hold this number if you can't or don't want to change the "song played" record. Then when records are added to the table, give each the next available number for that radio station. (Actually MySQL has a feature to do this automatically.) Then your query could look for this index <=13, or if records can be deleted, have an inner query that does a "select station, max(play) as maxplay group by station", then the outer query could select "where play>maxplay-13"

Update

To set up the MySQL auto-increment, see http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html. Note particularly the discussion around the sentence, "For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups." (Frankly, it's been a while since I used this so I can't supply a lot of detail.)

Is it acceptable to loop through the list of radio stations (as there are only 8) and fire off 8 separate queries? Something like

<?php
$stations = array('radio 538', 'radio veronica', 'slam fm', '3fm');
foreach ($stations as $station)
{
    $query = sprintf('SELECT * FROM table WHERE radioname="%s" ORDER BY time ASC LIMIT 0,13', $station);
    // do something
}
?>

[untested!]