Why don't use mysql ORDER BY RAND()?

Question

I saw many websites saying don't use ORDER BY RAND(), eg http://forge.mysql.com/wiki/Top10SQLPerformanceTips So I run a test, I have tested the speed and performance on 20k records table, 10k records among them have the username="username" :

SELECT username FROM testingspeed WHERE username='username' ORDER BY RAND();

The result :

Showing rows 0 - 29 (10,000 total, Query took 0.0119 sec).
id = 1 
select_type = SIMPLE
table = testingspeed
type = ref
posible_keys = username
key = username
key_len = 32
ref = const
rows = 3225
Extra = Using where; Using index; Using temporary; Using filesort

since it took 0.0119 seconds only to execute the query, it should be very good speed, why people still say DON'T use ORDER BY RAND()? Why 3225 rows are affected only? Why not 10,000 rows are affected?

Answer 1

The problem of ORDER BY RAND() is that as your explain tells you the "Using temporary" and the "Using filesort". For each request a temporary table is created and sorted. Thats a pretty heavy operation. It will probably not matter when your database is not under heavy load but it will cost a lot of performance.