How is using Join faster than using just Rand() in MySQL

How is

SELECT t.id 
FROM table t 
JOIN (SELECT(FLOOR(max(id) * rand())) AS maxid FROM table) 
AS tt 
ON t.id >= tt.maxid 
LIMIT 1

faster than

SELECT * FROM `table` ORDER BY RAND() LIMIT 1

I am actually having trouble understanding the first. 开发者_开发问答Maybe if I knew why one is faster than the other I would have a better understanding.

*original post @ Difficult MySQL self-join please explain

You can use EXPLAIN on the queries, but basically:

In the first you're getting a random number (which isn't very slow), based on the maximum of a (i presume) indexed field. This is quite quick, i'd say maybe even near-constant time (depends on the implementation of the index hash?)

Then you're joining on that number and returning only the first row that's greater then, and because you're using an index again, this is lightning quick.

The second is ordering by some random function. This has to, but you'll need to look at the explain for that, do a FULL TABLE scan, and then return the first. This is ofcourse VERY expensive. You're not using any indexes because of that rand.

(the explain will look like this, showing that you're not using keys)

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  table    ALL    NULL    NULL    NULL    NULL    14  Using temporary; Using filesort