Neil, We never delete from primary tables. No questions asked! We would just mark a entry as deleted, and not select from it.
Another option you can do to solve your deletion problem is, select 35 rows for example, when you really only want 30. That way, you can have extras, if say #20 is not available. There are many options, we have even in some cases, created a table and run the order by rand query every 5 minutes and just have it update a table. And then we just do a select from that secondary table. So every 5 minutes you have new random items. Donny > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of > Neil Gunton > Sent: Monday, March 08, 2004 3:11 PM > To: Donny Simonton > Cc: [EMAIL PROTECTED]; 'MySQL' > Subject: Re: ORDER BY RAND() performance > > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
