Ignoring the limit problem for just a moment, what are the performance characteristics? Does this form have a less steep rows vs. time curve?
As for the limit problem, that sounds a bit like a bug in MySQL and should be addressed as such. -JF > -----Original Message----- > From: MySQL List [mailto:[EMAIL PROTECTED]] > Sent: Thursday, June 27, 2002 2:44 PM > To: Jon Frisby > Cc: mysql > Subject: Re: Rand slowness. > > > I tried this, but it seems to be ignoring the LIMIT 3, since it > is returning > all the rows. > > > > Try: > > SELECT RAND() AS r, * FROM table ORDER BY r LIMIT 3; > > > > Off the top of my head, I'd guess that the reason for the > slowdown has to > do > > with MySQL doing a new RAND() call for every comparison in the sort > > operation -- it wont stop sorting until it happens to get values back in > the > > comparisons indicating that it has a "correct" ordering. The > odds of this > > happening in a timely fashion *decrease* quite quickly as the number of > > comparisons increases. > > > > Selecting a random value *PER ROW* ensures that a comparison between any > two > > given rows will always have the same result the second time you > try it as > it > > did the first time you try it and will thus ensure deterministic and > > reasonable sorting behavior. > > > > -JF > > > > > -----Original Message----- > > > From: MySQL List [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, June 27, 2002 12:05 PM > > > To: mysql > > > Subject: Rand slowness. > > > > > > > > > Hi, > > > Is there a quicker way to get a selection of random rows than RAND()? > > > I basically use: > > > > > > SELECT * FROM table ORDER BY RAND() LIMIT 3; > > > > > > But I find that as the # of rows increases in table, it keeps > > > taking longer > > > and longer to get a result back. And I am talking a large increase, > > > basically 50 rows took about 1 second, 700 rows now takes almost a > minute. > > > > > > I am not sure why the # of rows would affect RAND. > > > > > > I am using the latest version of Mysql and php4 to get the rows. > > > > > > > > > --------------------------------------------------------------------- > > > Before posting, please check: > > > http://www.mysql.com/manual.php (the manual) > > > http://lists.mysql.com/ (the list archive) > > > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > > To unsubscribe, e-mail > > > <[EMAIL PROTECTED]> > > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php