----- Original Message -----
From: "Mike Zornek" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, November 29, 2004 12:08 PM
Subject: SQL question.... Trying to improve upon my PHP solution.
> 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?
>
I don't think this is a question about SQL at all; I think you already know
how to write the SQL to select, insert, update or delete rows.
I think that what you really want to know is if there is a more efficient
way to shuffle your rows and that is more a question of choosing a good
programming algorithm and plugging in the SQL you already know.
I have to admit I'm curious about why you need to do this. In many years of
database work, I've never seen the need to "shuffle" a table before. By the
way, could you clarify what you mean by "shuffle"? Do you mean that you want
to completely randomize every row and put it in some new, randomly-chosen
"slot" within the table? Or are you only moving the bottom row to the top
and pushing each of the others down one slot?
I suspect that you might be able to avoid this shuffle entirely and simply
choose rows at random based on their primary key without inserting,
updating, or deleting anything. You probably only have to show the rows in a
random sequence without actually physically moving them within the table. In
that case, simply get a list of the primary key values for every row of the
table and assign them to an array; then use a random number generator to
select keys from the array at random. Of course, this still gives you the
likelihood of choosing some records twice or multiple times and some not at
all; you'd need to make the algorithm recognize when it has grabbed a given
key already so that it ignores it if it is chosen a second time.
If PHP has a data structure analagous to Java's vector, it would be a lot
better choice: you simply put each primary key in its own slot of the
vector, select each key at random via the random number generator, and after
you've selected the row that corresponds to that primary key DELETE THE KEY
FROM THE VECTOR. That ensures that no row is selected twice and every row
gets selected at some point.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
