EXPLAIN EXTENDED

How to create fast database queries

Archive for March 2nd, 2009

Zen update

Comments enabled. I *really* need your comment

Yesterday I wrote an article about selecting random rows efficiently.

But today on Stack Overflow:

Hi

I wish to attach a column to my table which will be a random number from a sequential list = to the number of rows.

So, if my table had 999 rows, then the numbers 1 to 999 would be assigned randomly and uniquely.

Now, I figured that I could add a dummy TempRandomColumn=Rand(), sort by that and add the numbers sequentially using PHP. But that means 999 MySQL statements.

Is there a way to do this using a single MySQL statement?

Thanks for any pointers.

Well, it's just that simple:

Creating tables here:

and performing an update:

SET @r := 0;
UPDATE t_zen
SET    zen_order = (@r := @r + 1)
ORDER BY
       RAND(20030302)

ORDER BY RAND() certainly has some beauty in it.

Written by Quassnoi

March 2nd, 2009 at 11:00 pm

Posted in MySQL