|
|
Posted by "Richard Lynch" on 10/11/05 01:51
On Mon, October 3, 2005 2:39 pm, Graham Anderson wrote:
> what would be the best way to make MYSQL's RAND() more random ?
>
> my random result set always seems pretty predictable. The first
> record
> in the result set always seems to be the same 4 tracks :(
> Would adding some kind of random seed like
> RAND($randomNumberGenerator)
> work ?
> If so, how do you implement this ?
>
>
> $sql = "SELECT media.id,
> artist.name as artist,
> artist.spanish as bio,
> artist.purchaseLink,
> artist.picture,
> media.spanish as trackName,
> media.path,
> media.quality,
> mediaType.id as mediaType
> FROM artist, media, playlistItems, mediaType
> WHERE playlistItems.playlist_id = $myID
> AND playlistItems.media_id = media.id
> AND media.artist_id = artist.id
> AND media.mediaType_id = mediaType.id
> ORDER BY RAND($randomSeed) LIMIT 0, 30";
>
> many thanks in advance
If you are using a version of MySQL with sub-queries, you may want to
try wrapping the whole ORDER BY RAND() around it in an outer query...
Maybe the optimization/indexing of the results and the LIMIT are
somehow interacting to mess up RAND()
Just a guess, really.
Depending on the size of your library, you could get ALL the possible
tracks, and then use PHP and http://php.net/mt_rand to skip around
with mysql_result() at random. If your library of tracks is WAY too
big for that, don't do it.
You may also want to consider using more than just RAND() to choose
tracks.
For example, the playlist I generate for a coffeehouse every day has 3
factors:
1. How soon/recently the artist plays/played there.
2. Subjective "Quality" rating by the sound engineer of the track.
3. Random factor
http://uncommonground.com/
has a link to the radio right under the photo.
[site is getting a facelift soon, so this may change without notice]
So GREAT tracks and tracks of artists who are coming back soon or who
just played are MORE likely to get played, but there's always a
"random factor".
There are "multipliers" for each quantity, and the coffeehouse owner
can play around with them and "shape" the function to weigh it more
heavily toward any of the three factors.
In your case, I'd guess that you could consider factors like:
1. How recently was this song in a playlist.
2. How good was this song rated by the user
3. Random
None of this will actually "fix" your random issue, but by confusing
the order with other factors, might make the lack or randomness less
obvious and less bothersome. :-)
--
Like Music?
http://l-i-e.com/artists.htm
Navigation:
[Reply to this message]
|