MYSQL subquery and nested query optimization example analysis

Check 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 faster than the original 25 seconds.

Check 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' )

Especially time consuming

Also, 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 calculated using the function, 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 the player number, name, and the number of fines he has incurred, but only for those players who have at least two fines.

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 the player's number, name, and penalty number for each player. Next, this number becomes a column in the intermediate result. Then a condition is specified (NUMBER>=2); finally, the columns in the SELECT clause are retrieved.

Summarize

The above is all the content of this article about MYSQL subquery and nested query optimization example analysis. I hope it will be helpful to everyone. Interested friends can refer to: Examples of optimization techniques for slow subquery efficiency of MySQL IN statement, A brief discussion on the efficiency of MySQL subquery union and IN, etc. If there are any deficiencies, please leave a message and the editor will correct it in time.

Thank you friends for your support of 123WORDPRESS.COM!

You may also be interested in:

Tutorial on column subquery and row subquery operations in MySQL
Detailed explanation of query examples within subqueries in MySql
Subquery examples in MySQL
Detailed explanation of MySQL subqueries (nested queries), join tables, and combined queries
MySQL Tutorial: Subquery Example Detailed Explanation

<<: js realizes 3D sound effects through audioContext

>>: Use the more, less, and cat commands in Linux to view file contents