Analysis of MYSQL Subquery and Nested Query Optimization Instances

Query the top 100 highest scores in game history

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, which is 10,000 times higher than the original 25 seconds

Inquire about the best results of the game that 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' ) 

Specially consumed time

In addition, like:

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 evaluated by the function once, and needs to be adjusted to:

Sql code

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

//After the change
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 out the player's number, name and the number of fines he caused, but only for those players who have been fined at least twice.

For a more compact query, 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 each player's number, name, and penalty number. Next, this number becomes a column in the intermediate result. Then one condition is specified (NUMBER > = 2); Finally, get the columns in the SELECT clause.

Summarize

The above is the article on MYSQL subquery and nested query optimization instance analysis of all the content, I hope to help you. Interested friends can refer to: mysql in statement subquery efficiency slow optimization skills example, talk about mysql subquery union and in efficiency, etc. If there are deficiencies, please leave a message, this site will be corrected in time.

Thank you friends for your support to this site!