欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MYSQL子查询和嵌套查询优化实例解析

程序员文章站 2022-04-29 09:05:46
查询游戏历史成绩最高分前100 sql代码 select ps.* from cdb_playsgame ps where ps.credits=(select...

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

sql代码

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

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

 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  

像查询里:

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

特别消耗时间

另外,像:

from_unixtime(ps1.dateline, '%y%m%d') = '$todaytime' 

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

sql代码

and ps1.dateline >= unix_timestamp('$todaytime')  

//更改后
sql代码

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

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子查询和嵌套查询优化实例解析的全部内容,希望对大家有所帮助。感兴趣的朋友可以参阅:、等,如有不足之处请留言,小编会及时更正。

感谢朋友们对网站的支持!