MySQL Performance: MySQL => RAND() Efficiency on well indexed columns

RAND() Efficiency on well indexed columns [message #193]

Tue, 26 September 2006 09:11

Speeple
Messages: 91
Registered: August 2006

Member

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.

Martin Gallagher | Speeple: The latest news

Report message to a moderator