MySQL random query efficiency comparison

SelectPrimary_count asPrimarycount, Primary_score asPrimaryscore, Junior_count asJuniorcount, Junior_score asJuniorscore, Senior_count asSeniorcount, Senoir_score asSenoirscore, Total_score asTotalscore, Pass_score asPassscore fromPd_paper PwhereP.is_valid= '1'Order  by RAND() limit1Analysis:ORDERthe rand () function cannot be used in a by clause because it causes the data column to be scanned multiple times. The test found this very inefficient. A library of more than 150,000 articles, query 5 data, to 8 seconds or more. You cannot UseAcolumn  with RAND()Values inchAnORDER  byclause, becauseORDER  bywould evaluate thecolumnmultiple times. More efficient approach: query Max (ID)* Rand() to get the data randomly. SELECT *  from`Table` asT1JOIN (    SELECT ROUND(RAND()     *(SELECT MAX(ID) from`Table`)) asID) asT2WHERET1.id>=t2.idORDER  byT1.idASCLIMIT5but this will produce 5 consecutive records. The solution can only be one query at a time, query 5 times. Even so it is worth it, because 150,000 of the tables, the query only need 0.01 seconds less than. Using the join syntax allows for true randomness. SELECT *  from`Table` WHEREId>=(SELECT  Floor(MAX(ID)* RAND()) from`Table` ) ORDER  byID LIMIT1; Make a statement perfect, plus min (id) judgment. If you do not add the min (id) judgment, the result is half the time that you always query to the first few rows in the table. Complete statement: ①whereclausesSELECT *  from`Table` WHEREId>=(SELECT  Floor(RAND()*((SELECT MAX(ID) from`Table`)- (SELECT MIN(ID) from`Table`))+(SELECT MIN(ID) from`Table`))) ORDER  byID LIMIT1; ②Join SELECT *  from`Table` asT1JOIN (    SELECT ROUND(RAND()     *((SELECT MAX(ID) from`Table`)-(SELECT MIN(ID) from`Table`)) +(SELECT MIN(ID) from`Table`)) asId asT2WHERET1.id>=t2.idORDER  byT1.id LIMIT1; #随机查询一套考卷定义SELECTP.primary_count asPrimarycount, P.primary_score asPrimaryscore, P.junior_count asJuniorcount, P.junior_score asJuniorscore, P.senior_count asSeniorcount, P.senoir_score asSenoirscore, P.total_score asTotalscore, P.pass_score asPassscore fromPd_paper asPJOIN (    SELECT ROUND(        RAND()         *((SELECT MAX(ID) fromPd_paper)-(SELECT MIN(ID) frompd_paper)) +(SELECT MIN(ID) frompd_paper))  asID) asP2WHEREP.id>=p2.idORDER  byP.id LIMIT1; Finally, the two statements are queried in the program 10 times, the former takes time0.147433seconds The latter takes time0.015130second, it seems that the syntax for join is much higher than the efficiency of using functions directly in the where. Attach a complex SQL: Randomly query all item information by topic type (three types)SELECT *  from (    Selectp.id asID, P.title astitle, P.question asquestion, P.answer asanswer, P.crt_time asCrttime fromPd_problem PJoin        (SELECT ROUND(RAND()             *((SELECT MAX(pp.id) fromPd_problem PP)-(SELECT MIN(pp.id) fromPd_problem pp)) +(SELECT MIN(pp.id) fromPd_problem pp)) aspid asP2whereP.id>=P2.pid andP.is_valid= '1'     andP.paper_type= '1'     andP.paper_class= '0'    Order  byP.id limit5 )  asT1Union  All  SELECT *  from (    Selectp.id asID, P.title astitle, P.question asquestion, P.answer asanswer, P.crt_time asCrttime fromPd_problem PJoin        (SELECT ROUND(RAND()             *((SELECT MAX(pp.id) fromPd_problem PP)-(SELECT MIN(pp.id) fromPd_problem pp)) +(SELECT MIN(pp.id) fromPd_problem pp)) aspid asP2whereP.id>=P2.pid andP.is_valid= '1'     andP.paper_type= '1'     andP.paper_class= '1'    Order  byP.id limit5 )  asT2Union  All SELECT *  from (    Selectp.id asID, P.title astitle, P.question asquestion, P.answer asanswer, P.crt_time asCrttime fromPd_problem PJoin        (SELECT ROUND(RAND()             *((SELECT MAX(pp.id) fromPd_problem PP)-(SELECT MIN(pp.id) fromPd_problem pp)) +(SELECT MIN(pp.id) fromPd_problem pp)) aspid asP2whereP.id>=P2.pid andP.is_valid= '1'     andP.paper_type= '1'     andP.paper_class= '2'    Order  byP.id limit5 )  asT3