Donny Simonton wrote: > One other option that we use > sometimes is say you need 30 results randomized, and you have an > auto-increment in your table. Create 30 random numbers, then do a select > with something like this: > > Select * from blabla where lkajsdlkjas IN (10, 43, 22, 8981, etc...) > > This works fairly well, but then again, I haven't benchmarked it in a while > and don't really remember how well it works. Actually, I just tried this on > a table with 43 million entries and it took 0.0004 seconds.
I was thinking about something similar, but how do you handle cases where you might have gaps in the auto-increment sequence? For example, if you delete record 100, and then one of the random numbers you generate happens to be 100, you will be short 1 record because it doesn't exist. If records never get deleted from the table then there's no issue, but in my application it does happen, so gaps will occur. I have looked around for an easy way to maintain a table with a key that acts like a "position" marker, but it doesn't seem to be out there. In other words, if you had a table with n records, then each record would have a field which has a value corresponding to the record's position in the table, from 1 to n. This "position" can be simply the order the records were inserted or the order that they exist on the disk - it doesn't really matter, since this position field would only be used for quick lookups in random selects anyway. Then, if record 6 is removed, record 7 would become record 6, record 8 would now be 7 and so on. I know you can maintain this sort of thing yourself, but it takes work to maintain consistency and it would be a nice feature to have. If this was available then "ORDER BY RAND()" optimization would be easy, since you could have the "sequence" field be a primary key and then just do "select where sequence in (...)", and it would be very fast. This could be done internally for "ORDER BY RAND()", or you could do the select yourself, using a better random number generator if you so wish. Thanks for the suggestions, -Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
