RAND() Efficiency on well indexed columns

I plan on having a large table, large as in lots of rows with contain a small amount of data.

The table holds integers acting as pointers.

Let’s say the table has 10 Mil rows to start with.

The following query would be efficient:

SELECT id FROM pointers WHERE user_id=1 LIMIT 5

Out of the 10Mil rows, this user “1” has 20 stored pointers, so the PRIMARY index should be efficient at extracting the resultset.

To make sure each pointer id gets a fair amount of audience I would like to randomise the results.

How efficient is “ORDER BY RAND()” on such a query?

My guess is quite efficient, MySQL would maybe create a temp table fast with the 20 results, then randomise them?

The table gets lots of simultaneous read/writes and is innodb type engine.

Right.

If you have 20 rows matching WHERE clause only these rows would be sorted so it would not be that bad.

Hi,

here you can find good solutions to MySQL problems.
http://www.fixya.com/support/t883221-mysql_efficiency

Ciao,
Shila.