Today I was looking at returning random records purely from MySQL but was limited with large tables in getting all random results (not just a random starting seed).
I tried a ColdFusion solution instead.
First get all the primary keys from the table in question and create an array of the values (this works for numeric and non-numeric keys):
Next create a list of random primary keys from our array:
With smaller tables you may need to do this step a little differently in case the randRange() returns duplicate Ids.
Finally we pass the list of random primary keys to the WHERE clause via cfqueryparam:
Depending on your situation you can cache the array of primary keys (in session or application scope) and refresh the list whenever your database changes. Then generate your random list of Id’s to pass to a query.
Without caching the list array (and using a table with 56,000 rows) I got the following times from the entire block of code (including getting the Id list):
When I cached the array in application scope, cftimer was giving me 0ms 
This was of course on my local machine. Although these results could be more accurate in a proper environment, they do show another solution in retrieving random query results.
You are open to duplicate rows.
Try something like:
Kris Brixon
March 21st, 2008
I note that this means you can’t use an ORDER BY clause in your SQL statement, which may be quite a disadvantage in some circumstances. If you can loop through the query with a cfloop instead of a cfoutput, you can achieve random results much faster and with only one hit on the database by just looping through a randomized list of query rows:
http://www.mollerus.net/tom/blog/2008/03/creating_randomlyordered_lists.html
Tom Mollerus
March 21st, 2008
@Kris - your comment didn’t quite come through.
I did say that duplicate records could be returned with randRange(), is that what you meant?
Michael Sharman
March 21st, 2008
@Tom - Why would you not be able to use an ORDER BY clause?
All we’re doing is saying “get me all the records which have ‘x’ as the primary key”. You can still use ORDER BY anything you want.
Also I’m not quite sure I get your post (in terms of a query). Will that not return a random list of row numbers? How will that help when looping to output a resultset?
Michael Sharman
March 21st, 2008
I’m going to guess that a stored proc would be even better code-wise and speedilicousness-wise. have you tested that? I ask sincerely, because I will be doing a lot of random row queries on a big table soon and wanna know
Kevin Sargent
March 23rd, 2008
[...] History is what was written down. « Options for accessing the ColdFusion 8 documentation Random database results with ColdFusion [...]
chapter31 » Blog Archive » Returning random results with MySQL
March 28th, 2008