mysql group by table b, inner join table a for a random

Question

here is my 2 tables, id for a inner join event. i want to do this thing:

in table b, there has 10 albums, i want random get out 4 albums. then each album select one record, the record is random position in the album.

so that i will get 4 records back (these 4 records with no duplicate id), then take these 4 records for a inner join query, to get the title from table a.

here just little records just for test. in the fact, i have 300,000 records in table a and 2,000,000 records in table b.

table a

+-----+-------+
| id  | title | 
+-----+-------+
| 1   | a1    |
+-----+-------+
| 2   | a2    |
+-----+-------+
| 3   | a3    |
+-----+-------+
| 4   | a4    |
+-----+-------+
| 5   | a5    |
+-----+-------+
| 6   | a6    |
+-----+-------+

table b

+-----+--------+
| id  | album  | 
+-----+--------+
|  1  | album1 | 
+-----+--------+
|  2  | album1 | 
+-----+--------+
|  3  | album1 | 
+-----+--------+
|  6  | album1 | 
+-----+--------+
|  2  | album2 | 
+-----+--------+
|  3  | album2 | 
+-----+--------+
|  5  | album3 | 
+-----+--------+
|  6  | album3 | 
+-----+--------+
|  3  | album4 | 
+-----+--------+
|  2  | album5 | 
+-----+--------+
|  4  | album5 | 
+-----+--------+
|  5  | album5 | 
+-----+--------+
|  1  | album6 | 
+-----+--------+
|  3  | album6 | 
+-----+--------+
|  2  | album7 | 
+-----+--------+
|  4  | album7 | 
+-----+--------+
|  1  | album8 | 
+-----+--------+
|  5  | album8 | 
+-----+--------+
|  3  | album9 | 
+-----+--------+
|  2  | album10| 
+-----+--------+
|  5  | album10| 
+-----+--------+

I am not good at mysql query. In my mind I would do

select * from b group by album order by random() limit 0,4

get back 4 album, then do a inner join query (this query not correct, how to check the b.id no duplicate?)

select * from b inner join a on b.id = a.id where (select id from b where b.album = '".$row['album']."'  order by random() limit 1)

I need an easy and quicker method, the best is just use one query. many thanks.

If I'm reading that right, you'd like to pick 10 albums at random from your list of albums, then pick out one track at random from each of those 10 albums? — Marc B
Put it another way, you want to randomly choose 4 tracks (which in turn allows you to select the album as well) but no two tracks can belong to same album? — Salman A
@Salman A, yes, no two tracks can belong to same album. I would query out the a.title for the random album by something like the cover detailed introduce. — fish man
If the tracks cannot belong to the same album, I'm afraid there is no simpler way than using a cursor to iterate over those 4 random albums, and performing a separate random-track-select-query on each of them. — Irfy

Alexey Alexey · Accepted Answer · 2012-05-06T22:11:41

AFAIR, "ORDER BY RAND()" is extremely slow solutions, especially on tables like you have (2 million+ records), so I'd recommend looking at something similar to these kind of articles first: http://www.greggdev.com/web/articles.php?id=6

So, you should know the number of records in your table before running the query and then do something like:

"SELECT * FROM `album` LIMIT 1 OFFSET " . rand(0,$count)

This will return you 1 random row a bit more efficiently, I believe.

Also, I think it's not a good idea to store album references as string in tracks table, you'd rather use a proper integer foreign key album_id referenced to albums.id. Then you can join both tables much fatser. If I were you, I'd do first:

ALTER TABLE `tracks` add column `album_id` int;
UPDATE `tracks` SET `album_id` = SUBSTRING(`album`,5);

Then, after doing this and combining with the solution above, launch something like:

"SELECT * FROM `album` INNER JOIN `tracks`ON `tracks`.`album_id` = `albums`.`id` LIMIT 1 OFFSET " . rand(0,$count)

mysql group by table b, inner join table a for a random

2 Answers