[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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
