For a random Bible verse block on one of my other sites, I needed a way to pull a single random record out of the database. The table that contains the Bible verses contains 31,102 records with each record being a single verse.
I first used the method I found on forums all over the net; that is to use “SELECT * FROM table ORDER BY RAND() LIMIT 1”. It worked, but the pages seemed to be loading more slowly. So, I went out to measure the speed of different techniques for pulling a single, random record.
Test Equipment
These tests were done on the computer sitting on (well, under) my desk, an Athlon 600mHz with 512mb RAM. The actual web server is a quad chip Xeon 2.4gHz, so obviously the real times are lower when they move on to the production server.
All queries were run using the command line in MySQL v4.0 through Windows 2000.
Test 1
My first test was to simply use the query ‘SELECT * FROM table ORDER BY RAND() LIMIT 1’. I ran it and MySQL returned that the query took 7.99 seconds. This seemed excessive and frankly not believable, so I ran it several times. Each time it hovered around the 8 second mark.
Here is when I realized my biggest error. I did not have a primary key assigned.
Test 2
The second test used the same query as test #1, but with the addition of a primary key to the table. I added a INT(11) field with auto increment on to act as the key. This led to a field with numbers from 1 to 31,102.
As a factor of percentage this made a huge difference. The average query time went down to 0.50 seconds. Much faster, but still too long. I know this query would be faster on the live web server, but it would also be run more often so speed is more of an issue.
Test 3
For my final test I thought that if I could pull a random number outside of MySQL in PHP and then query on that number it may be faster.
So, I tested the query “SELECT * FROM table WHERE id = ‘x'” where x was a number from 1 to 31,102. These queries came back in at a max of 0.02 seconds with most showing 0.00 seconds on my computer, so the live server would be even better (well, not better than 0).
This adds the overhead of the PHP srand() function, but measurements I did through the script measured under 0.01 seconds consitantly.
Conclusion & Caveats
I know was in a fairly rare position of knowing exactly how many records and knowing that my index field had no missing numbers. For a live database that may have records added and deleted this solution would not have been as effective. But, for my situation I found at least a 400x speed increase by using this solution.
Be First to Comment