MySQL randomly selects Resources-- Optimization

Following the previous article:

Http://blog.itpub.net/29254281/viewspace-2120294/

In the previous article, Order by rand () will have some performance problems when there is a large amount of data.

Set autocommit=false;set @ roomid:=-1;selectmin (roomid) into @ roomidfromroom_infowhereroomid > (selectfloor (max (roomid) * rand () + 1) fromroom_info) and state = 1bot update room_infosetstate = 2whereroomid = @ roomidand state = 1th select @ roomid;commit

The way to optimize is to randomly select a value from the largest ID.

This avoids sorting.

However, the application still needs to determine whether the number of rows affected by Update is 0. 5. If 0, it needs to be called again.

Big trick version:

Set autocommit=false;set @ roomid:=-1;select max (roomid) into @ roomid from room_info;set @ roomid:=floor (rand () * @ roomid+1); update room_infosetstate = 2whereroomid = coalesce ((select roomid from (select min (roomid) roomid from room_info where state=1 and roomid > @ roomid) a), (select roomid from (select max (roomid) roomid from room_info where state=1 and roomid < @ roomid) b) and state=1 and @ roomid:=roomid;select @ roomid;commit