MYSQL subquery and nested query optimization instance parsing, mysql nested instance Parsing

MYSQL subquery and nested query optimization instance parsing, mysql nested instance Parsing

Query the highest score of the game history score of 100

SQL code

SELECT ps.* FROM cdb_playsgame ps WHERE ps.credits=(select MAX(credits)  FROM cdb_playsgame ps1  where ps.uid=ps1.uid AND ps.gametag=ps1.gametag) AND ps.gametag='yeti3'  GROUP BY ps.uid order by ps.credits desc LIMIT 100; 

SQL code

SELECT ps.*  FROM cdb_playsgame ps,(select ps1.uid, ps1.gametag, MAX(credits) as credits FROM cdb_playsgame ps1 group by uid,gametag) t WHERE ps.credits=t.credits AND ps.uid=t.uid AND ps.gametag=t.gametag AND ps.gametag='yeti3'  GROUP BY ps.uid order by ps.credits desc LIMIT 100; 

The execution time is only 0.22 seconds, 10000 times higher than the original 25 seconds

Query the best game score of the day

SQL code

 SELECT ps. * , mf. * , m.username FROM cdb_playsgame ps LEFT JOIN cdb_memberfields mf ON mf.uid = ps.uid LEFT JOIN cdb_members m ON m.uid = ps.uid WHERE ps.gametag = 'chuansj' AND FROM_UNIXTIME( ps.dateline, '%Y%m%d' ) = '20081008' AND ps.credits = ( SELECT MAX( ps1.credits ) FROM cdb_playsgame ps1 WHERE ps.uid = ps1.uid AND ps1.gametag = 'chuansj' AND FROM_UNIXTIME( ps1.dateline, '%Y%m%d' ) = '20081008' ) GROUP BY ps.uid ORDER BY credits DESC LIMIT 0 , 50  

Like in the query:

AND ps.credits=(SELECT MAX(ps1.credits)   FROM {$tablepre}playsgame ps1 where ps.uid=ps1.uid AND ps1.gametag = '$game'   AND FROM_UNIXTIME(ps1.dateline, '%Y%m%d') = '$todaytime' ) 

Special time consumption

In addition, for example:

FROM_UNIXTIME(ps1.dateline, '%Y%m%d') = '$todaytime' 

Such a statement will cause the index to be invalid, because the value of each dataline needs to be calculated by using a function and adjusted:

SQL code

AND ps1.dateline >= UNIX_TIMESTAMP('$todaytime')  

// After modification
SQL code

 SELECT ps. * , mf. * , m.username FROM cdb_playsgame ps, cdb_memberfields mf, cdb_members m, (  SELECT ps1.uid, MAX( ps1.credits ) AS credits FROM cdb_playsgame ps1 WHERE ps1.gametag = 'chuansj' AND ps1.dateline >= UNIX_TIMESTAMP( '20081008' ) GROUP BY ps1.uid ) AS t WHERE mf.uid = ps.uid AND m.uid = ps.uid AND ps.gametag = 'chuansj' AND ps.credits = t.credits AND ps.uid = t.uid GROUP BY ps.uid ORDER BY credits DESC LIMIT 0 , 50  

For each player, find the player number, name, and number of the player that is fined, but only for those who have at least two fines.

For more compact queries, place a subquery in the from clause.

SQL code

SELECT PLAYERNO,NAME,NUMBER FROM (SELECT PLAYERNO,NAME,        (SELECT COUNT(*)        FROM PENALTIES        WHERE PENALTIES.PLAYERNO =           PLAYERS.PLAYERNO)        AS NUMBER     FROM PLYERS) AS PN WHERE NUMBER>=2 

The subquery in the from clause determines the number, name, and penalty number of each player. Next, this number is changed to a column in the intermediate result. Then specify a condition (NUMBER> = 2). Finally, obtain the columns in the SELECT clause.

Summary

The above is all the content about MYSQL subquery and nested query optimization instance resolution in this article. I hope it will be helpful to you. If you are interested, you can refer to the Optimization tips for slow subquery efficiency of mysql in statements and the efficiency of mysql subquery union and in statements. If you have any shortcomings, please leave a message, make corrections in a timely manner.

Thank you for your support for the website!