ORDER BY RAND Alternative

If you're reading this chances are you know everything you need to know about why using ORDER BY RAND() in MySQL is not such a good idea so I'll skip all of that and get right to the alternative.

Add a new column to your database table called rand_id and make it large enough to hold a UNIX timestamp. The index is extremely important. This table modification may take awhile if you have a large table.

ALTER TABLE my_table ADD `rand_id` INT NOT NULL ,
ADD INDEX(rand_id)

With the new column in place, setup a cron job which executes an SQL statement to update that column once a day (or more often if you wish). What this does is give each row a random ordering ID starting with the current time plus a random portion of the seconds in a day.

UPDATE my_table
SET rand_id = (UNIX_TIMESTAMP() + (RAND() * 86400))

To fetch a random row you use a query like this.

SELECT *
FROM my_table
WHERE rand_id > UNIX_TIMESTAMP()
LIMIT 1

Here's a few numbers from a test table with 1,572,851 rows. The server used isn't exactly optimized to be a database server either.

Selecting a random row using ORDER BY RAND() takes 37 seconds.
Executing the query to update the rand_id column takes 110 seconds.
Selecting a random row after the rand_id column is updated takes 0.0012 seconds.

Now if we needed a random row every second of every day, we would need 86,400 rows.

86,400 * 37 = 3,196,800 seconds spent selecting random rows. It would take more than a month to select a days worth or rows using ORDER BY RAND().

86,400 * 0.0012 = 103.68 seconds spent fetching random rows. If we add the 110 seconds needed to generate the rand_id column values that brings us to 213.68 seconds. That's less than 5 minutes spent fetching a days worth of random rows. Huge improvement over the ORDER BY RAND option.

Now, this solution can have a few serious drawbacks depending on your needs and table size.

The longer it has been since the rand_id column was generated, the more predictable the random row will be since you can make a note of the rows that have been selected throughout the day and narrow things down considerably.

Tables with less than roughly 90,000 rows are in danger of producing the same result multiple times in a row for seconds to minutes at a time.

Putty Ubuntu SSH Autologin

With the addition of a Windows Vista computer to my small network I find myself needing to get into the Ubuntu systems via SSH when I'm on the Vista computer. This is simple with Putty, but entering my username and password every time is annoying.

I've already setup the rest of the systems on my network to have Ubuntu login to SSH automatically. I don't see why I can't have the Windows Vista computer do the same with Putty.

Having already setup the other systems, I knew I needed a key to add to the authorized_keys file on the remote system, and by looking at Putty I knew I needed a private key file.

I tried using ssh-keygen on the Ubuntu system to generate keys, but the format used for the private key was different looking than the file format of the PPK file Putty was looking for.

In order to get the key pairs I needed a tiny utility called puttygen from the Putty downloads page.


Here is what the interface looked like. I left everything default and clicked Generate.

After moving my mouse around to generate randomness while it generated a new pair of keys I clicked the Save Private Key button, selected yes when asked if I wanted to save the key without a passphrase, then selected the place I wanted to store the PPK file.

Next I copy and pasted the public key from the text area that showed up above the buttons and below where it says Public key for pasting into OpenSSH authorized_keys file, into my authorized_keys file on the remote system via nano in an open SSH session.

After closing Puttygen I closed my existing SSH session out and re-opened Putty to be presented with the settings dialog.


I entered my username in the Connection -> Data -> Autologin Username field as shown here.

Then I selected the PPK file I saved earlier in the Connection -> SSH -> Auth screen.

Viola! Autologin to Ubuntu via SSH using Putty worked like a charm.