I have a table of members, about 13,000 rows.
Each night I need to shuffle the table. I have a small int column called random_position. Currently I am creating a position list (based on the count of the members), shuffle it, then while iterating through the members assigning them a a position.
$time_start = microtime_float();
$member = new DataObjects_Member(); $number_of_rows = $member->find();
$positions = array(); for ($i = 1; $i <= $number_of_rows; $i++) { $positions[] = $i; }
shuffle($positions);
while ($member->fetch()) {
// choose a position from the bottom $position = array_pop($positions);
// set this member with that position $member->setRandom_position($position); $member->update(); }
Of course by doing it this way I'm running 13,000 SELECT calls and 13,000 UPDATE calls which can be processor intensive. As of now I'm timing it at 225 seconds but this machine is faster than server.
Is there a simpler / faster SQL query I could use?
Thanks!
~ Mike ----- Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: <http://MikeZornek.com>
Mike,
Your solution is way too complicated (it makes my head hurt).<g> Try this:
set @n=0; update tmp set rnd = @n := @n + 1 order by RAND()
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
