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.