Difficult MySQL self-join please explain

This is supposed to be able to return a random post id. It is also said to be the fastest method using MySQL.

SELECT t.id 
FROM table t 
JOIN (SELECT(FLOOR(max(id) * rand())) AS maxid FROM table) 
AS tt 
ON t.id >= tt.maxid 
L开发者_高级运维IMIT 1     

Can't seem to wrap my head around this, please help.

You pick a random ID number from your table with the "joined table"

 SELECT(FLOOR(max(id) * rand())) AS maxid FROM table

This will only return something you can use if the id's don't have holes in them (consequtive integers). THat's why you're joining on this

 ON t.id >= tt.maxid 

You'll get all the id's that are HIGHER then your random number. The limit then gets the first of those.

So an example: say you have these entries in table: 1, 2, 5, 7, 8.

The floored random number will be smaller then 8, e.g. 3. You'll join will give you 5, 7 and 8 but returns only 5 because of the limit

First, formatting helps

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

Let's look at subquery inside JOIN first:

• rand() returns random value in range [0, 1] (e.g. 0.999, 0.251, 1)
• max(id) means that your query will return maximal value of column id from specified table
• Floor rounds input number down to the nearest integer (e.g. Floor(5.1) == 5, Floor(5.9) = 5)

hence, the subquery will return you random integer (due to FLOOR) number in the
range [0, 1]

"LIMIT 1" means that your query will return only one row from resulting set.

Hope this enough. Feel free to ask more details