On Monday 08 March 2004 14:14, Neil Gunton wrote: > [EMAIL PROTECTED] wrote: > > If your infact (sounds like) storing the pictures meta-data > > (name, size, owner, etc) and the data (blob of some kind) .. I > > would definately break up the design into 2 tables. That way > > when dealing with the meta-data table (your RAND() query) there > > is much less data that needs to be traversed to get your answer, > > which should result in a faster query. > > Thanks! This is definitely good advice, but unfortunately it > doesn't solve the RAND() slowness. I have been testing with a > separate table that ONLY contains the id of the pics, and as it > grows toward 100,000 records this simple query does get noticeably > slower: > > SELECT * FROM visible_pics ORDER BY RAND() LIMIT 30; > > Where visible_pics just has two numeric ID fields (pic_id and > doc_id). It doesn't seem to matter if I make pic_id a primary key > or not. I think I've reduced it to pretty much the minimal case, > given that I want a random selection of ALL the records. > > I don't know the internals of how MySQL could optimize this sort of > thing, but I was thinking that perhaps there was some kind of > internal trickery it could do to select random record positions and > then get those very quickly, without having to traverse the entire > table. I think if the table has no varchar fields then it should be > easy (at least in MyISAM) to calculate the record position based on > the record number. So I think it *should* in theory be possible to > optimize this, but I just don't know if anyone has realized that > it's an issue, or if they are planning on doing anything about it. > Any insights from MySQL internal developers? Or should I be posting > this to the "internals" list? > > Thanks again, > > -Neil
an alternative to the order by rand() with large record sets is to pick a random starting point "limit $randPoint, 30" don't know if its a viable solution to your situation, but it limits you to 2 querys (row count, fetch) rather then the 30 (fetch 1 x 30) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
