0

I have a Mysql database table with ~10000 songs. One column is the last time they've been played. I want to randomly select a song, giving more weight to the least played songs. It should be a fast query, so maybe we don't need to count the number of times a song's been played and just use the 'last_play' column to calculate the weight.

I'm using PHP and Mysql and I'm not sure if I can do the query with just SQL or if I need to get all 10.000 results and then make calculations using PHP.

Could you please help me with the query? Maybe there is a better approach to solve the problem?

The table:

CREATE TABLE `songs` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT '0',
    `filename` VARCHAR(150) NULL DEFAULT '0',
    `last_play` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB
;
Francisco R
  • 4,032
  • 1
  • 22
  • 37
  • it would be hard without looking at the table schema. – Sameer Kumar Jain Apr 08 '16 at 17:49
  • 3
    A few more constraints on the problem would help too. Can you just select from those songs that haven't played in a while, or do you always want the chance to select the most played as well? If the former, SELECT song FROM table ORDER BY last_played ASC LIMIT 100 and pick a random number from 0-99. – user3486184 Apr 08 '16 at 17:52
  • Interesting...I can't figure out a pure sql solution. I would try to sort the table by last_play time and while transversing the resultset pick one with a certain probability. – marcellorvalle Apr 08 '16 at 17:54
  • After reading @user3486184 answer I think something like this should be interesting (this is a valid mysql query?): SELECT song FROM (SELECT song, FROM table ORDER BY last_played ASC LIMIT 100) ORDER BY rand() LIMIT 1 – marcellorvalle Apr 08 '16 at 17:59
  • Using your approach would definitely help those songs to be played but 9900 would be excluded, so not a "real" random. Using a high LIMIT like 9000 would not help too much either. I just want to "help" the least played songs to be chosen. Maybe using a dynamic limit ? – Francisco R Apr 08 '16 at 18:10
  • What about converting the date to an integer (http://stackoverflow.com/questions/1921574/how-to-convert-datetime-to-a-number-in-mysql) and then combine this number somehow with the rand() function result? – marcellorvalle Apr 08 '16 at 18:17
  • 2
    http://stackoverflow.com/questions/2417621/mysql-select-random-entry-but-weight-towards-certain-entries might help – John Boker Apr 08 '16 at 18:20
  • very interesting @JohnBoker! I'm going to test `ORDER BY -LOG(RAND())*last_play`. Maybe I'll need to create a play_count column since last_play is timestamp. – Francisco R Apr 08 '16 at 18:38

0 Answers0