Mysql group on table b, internal connection table a for random

Question

here are my 2 tables, id for internal join event. I want to do this:

in table b, there are 10 albums , I want 4 albums random output. then each album select one record , the record will be a random position in the album.

so that I get 4 records back (these 4 records with no duplicate id) , then take these 4 records for an internal connection request to get the title from table a .

here are just small entries just for the test. in that I have 300,000 entries in table a and 2,000,000 entries 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 opinion, I would do

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

get back 4 albums, then execute an internal connection request (this request is not correct, how to check 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 a simple and quick method, it is best to use a single request. many thanks.

Answer 1

AFAIR, "ORDER BY RAND ()" is an extremely slow solution, especially on tables like you (2 million records), so I would recommend that you first look at something similar to these articles: http://www.greggdev.com/ web / articles.php? id = 6

So, you should know the number of records in the 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 little more efficiently, I reckon.

In addition, I believe that it is not recommended to store album records as a string in the track table; you would prefer to use the correct integer foreign key album_id, which the album refers to. Then you can join both tables a lot of fatser. If I were you, I would 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, run something like:

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

Answer 2

Since I am not an expert on MySQL and PHP, I will try to use pseudocode and general SQL. I renamed your tables to albums and tracks for readability.

• First enter four random entries in your PHP application:

   select id from albums order by random() limit 4 

• Secondly, we sort through the resulting set of four identifiers and select the appropriate tracks (pseudo-php):

   foreach($album_ids as $id): execute_query("select id from tracks where album_id = ? order by random(), limit 1", $id) 

It’s not obvious to me how you fit your tracks with your albums. You should have something like tracks.album_id as a foreign key for albums.id that I designed my queries. You must adapt if necessary, the basic logic of my decision should remain the same.