子查询效率

查询游戏历史成绩最高分前100

Sql代码

?

1 2 3 4

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代码

?

1 2 3 4 5

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;

执行时间仅为0.22秒，比原来的25秒提高了10000倍

查询当天游戏最好成绩

Sql代码

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

像查询里：

?

1 2 3

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

特别消耗时间 

另外，像：

?

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

这样的语句会导致索引无效，因为对每个dataline的值都需要用函数计算一遍，需要调整为：

Sql代码

?

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

//更改后 
Sql代码

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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

对于每个球员，找出球员号码，名字以及他所引起的罚款的号码，但只是针对那些至少有两次罚款的球员。 

更紧凑的查询，在FROM子句中放置一个子查询。 

Sql代码

?

1 2 3 4 5 6 7 8 9

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

FROM子句中的子查询决定了每个球员的号码，名字和罚款的编号。接下来，这个号码变成了中间结果中的一列。然后指定了一个条件（NUMBER>=2）;最后，获取SELECT子句中的列。

总结

以上就是本文关于MYSQL子查询和嵌套查询优化实例解析的全部内容，希望对大家有所帮助。感兴趣的朋友可以参阅：mysql in语句子查询效率慢的优化技巧示例、浅谈mysql的子查询联合与in的效率等，如有不足之处请留言，小编会及时更正