On 18/10/06, Dr Lovegrove <[EMAIL PROTECTED]> wrote:
On 18/10/06, sicoffey
<[EMAIL PROTECTED]> wrote:
>
> Thanks, interesting to know that MP3 might not solve the problem.
No, it wont. The problem is with the SQL query which randommix
uses. Essentially:
SELECT * FROM tracks ORDER BY RAND() LIMIT 1
Just had a bit more of a play..
Ok, so the original query is this, and it takes over 8s:
SELECT me.id, me.url, me.content_type, me.title, me.titlesort,
me.titlesearch, me.album, me.tracknum, me.timestamp, me.filesize,
me.disc, me.thumb, me.remote, me.audio, me.audio_size,
me.audio_offset, me.year, me.secs, me.cover, me.vbr_scale, me.bitrate,
me.samplerate, me.samplesize, me.channels, me.block_alignment,
me.endian, me.bpm, me.tagversion, me.drm, me.moodlogic_id,
me.moodlogic_mixable, me.musicmagic_mixable, me.musicbrainz_id,
me.playcount, me.lastplayed, me.lossless, me.lyrics, me.rating,
me.replay_gain, me.replay_peak FROM tracks me
LEFT JOIN genre_track genreTracks ON ( genreTracks.track = me.id )
WHERE ( audio = '1' AND genreTracks.genre IN ( '21', '26', '17', '22',
'18', '30', '16', '23', '29', '25', '27', '28', '20', '24', '19', '31'
)
AND ( ( lastPlayed IS NULL ) OR ( lastPlayed < '1161171000' ) ) )
ORDER BY RAND() LIMIT 1
If I strip out all the columns and just get it to sort the tracks id's
like this:
SELECT me.id FROM tracks me
LEFT JOIN genre_track genreTracks ON ( genreTracks.track = me.id )
WHERE ( audio = '1' AND genreTracks.genre IN ( '21', '26', '17', '22',
'18', '30', '16', '23', '29', '25', '27', '28', '20', '24', '19', '31'
)
AND ( ( lastPlayed IS NULL ) OR ( lastPlayed < '1161171000' ) ) )
ORDER BY RAND() LIMIT 1
It now takes under a second, and you're left with the random track ID, which
should be all it needs..
--
- Dr Lovegrove
Free Moose for all at:
http://www.rusticrhino.com/drlovegrove
_______________________________________________
discuss mailing list
[email protected]
http://lists.slimdevices.com/lists/listinfo/discuss
