Help getting, or displaying, random records in ColdFusion from a MySQL query?

Question

I've got a jquery roller/scroller that displays snippets of records returned from my 'Helpful Hints' database table query. I want to keep the scroller to about 15 records but not always the first 15 records.

Is it more efficient to write a query like:

SELECT *
FROM table
ORDER BY RAND()
LIMIT n

Which returns a random result or do I return the whole query and have my ColdFusion component serve up a random number of the query result?

The future of my scroller will include random records from my 'Items for Sale' table as well, so I need to keep that in mind.

Answer 1

Returning the entire result set and them throwing most of it out would definitely be less efficient.

When you get to the point of including data from other tables you can run another query like the one you've got and then shuffle the results together in ColdFusion.

For the sake of completeness, something like this is possible, though too slow to be practical:

( SELECT * FROM table1 )
UNION
( SELECT * FROM table2 )
ORDER BY RAND()
LIMIT 30 

Answer 2

Unless you are caching a relatively small and static query, it is usually more efficient to randomize the records on the database side. That way you are only pulling 15 records each time, not all records in the table.

Answer 3

I agree with bemace that returning a large result set of values that won't be used to Coldfusion is a waste of resources that can never be recouped.

But be careful about using MySQL's RAND() function for ordering - once you're over 100,000 records, it doesn't scale well (see graph), and you should look at using alternatives.