SELECT count(a.`type_id`) as promotion_count, a.`account`,a.`server_id` FROM
(SELECT `type_id`,`account`,`server_id` FROM `yx_hand_player_type` WHERE `type`='1' ) a
right join
(SELECT `f_account`,`f_server_id`,`gf_account`,`gf_server_id` from `yx_hand_player_type` WHERE `type`!='1') b
on ((a.`account`=b.`f_account` and a.`server_id`=b.`f_server_id`) or (a.`account`=b.`gf_account` and a.`server_id`=b.`gf_server_id`))
group by a.`account`, a.`server_id` order by promotion_count desc limit 0,10
============================================================================
一共花去时间 4.785s表的结构如下
主键:
type_id唯一索引:
account
server_id
type 索引:
f_account
f_server_id索引:
gf_account
gf_server_id
=====================补充下
去掉 limit 0,10
=================
总共查询记录 1,715 条
耗时也是 4.8657=========================================
小弟请教各位大虾
如何可以更加提升速度呢
5秒的延迟客户不接受勒
(SELECT `type_id`,`account`,`server_id` FROM `yx_hand_player_type` WHERE `type`='1' ) a
right join
(SELECT `f_account`,`f_server_id`,`gf_account`,`gf_server_id` from `yx_hand_player_type` WHERE `type`!='1') b
on ((a.`account`=b.`f_account` and a.`server_id`=b.`f_server_id`) or (a.`account`=b.`gf_account` and a.`server_id`=b.`gf_server_id`))
group by a.`account`, a.`server_id` order by promotion_count desc limit 0,10
============================================================================
一共花去时间 4.785s表的结构如下
主键:
type_id唯一索引:
account
server_id
type 索引:
f_account
f_server_id索引:
gf_account
gf_server_id
=====================补充下
去掉 limit 0,10
=================
总共查询记录 1,715 条
耗时也是 4.8657=========================================
小弟请教各位大虾
如何可以更加提升速度呢
5秒的延迟客户不接受勒
show index from yx_hand_player_type;
explain SELECT count(a.`type_id`) as promotion_count, a.`account`,a.`server_id` FROM
(SELECT `type_id`,`account`,`server_id` FROM `yx_hand_player_type` WHERE `type`='1' ) a
right join
(SELECT `f_account`,`f_server_id`,`gf_account`,`gf_server_id` from `yx_hand_player_type` WHERE `type`!='1') b
on ((a.`account`=b.`f_account` and a.`server_id`=b.`f_server_id`) or (a.`account`=b.`gf_account` and a.`server_id`=b.`gf_server_id`))
group by a.`account`, a.`server_id` order by promotion_count desc limit 0,10;给出以上信息。
`type_id` int(11) NOT NULL auto_increment,
`account` varchar(100) collate utf8_bin NOT NULL,
`server_id` int(11) NOT NULL default '0',
`type` char(1) collate utf8_bin NOT NULL default '0',
`f_account` varchar(100) collate utf8_bin NOT NULL default '0',
`f_server_id` int(11) NOT NULL default '0',
`gf_account` varchar(100) collate utf8_bin NOT NULL default '0',
`gf_server_id` int(11) NOT NULL default '0',
PRIMARY KEY (`type_id`),
UNIQUE KEY `account` (`account`,`server_id`,`type`),
KEY `f_account` (`f_account`,`f_server_id`),
KEY `gf_account` (`gf_account`,`gf_server_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=8979 ;
可以去看下我在cu上的帖子
http://bbs3.chinaunix.net/viewthread.php?tid=1542157&page=1&extra=page%3D1
from yx_hand_player_type a
where exists (
select 1 from yx_hand_player_type
where (f_account=a.account and f_server_id=a.server_id)
or (gf_account=a.account and gf_server_id=a.server_id)
)
group by a.account,a.server_id
看楼主不太愿意提供更多的信息。select f_account,f_server_id,count(*)
from (
select f_account,f_server_id
from yx_hand_player_type WHERE `type`='1'
union all
select gf_account,gf_server_id
from yx_hand_player_type WHERE `type`='1'
) t
group by 1,2
SELECT COUNT(a.`type_id`) as promotion_count, a.`account`,a.`server_id`
FROM `yx_hand_player_type` a, `yx_hand_player_type` b
WHERE a.`type` = '1' and b.`type` <> 1 and
(a.`account` = b.`f_account` and a.`server_id` = b.`f_server_id`) or
(a.`account` = b.`gf_account` and a.`server_id` = b.`gf_server_id`)
GROUP BY a.`account`, a.`server_id`
ORDER BY promotion_count DESC LIMIT 0,10
5楼的语句跟楼主原先的意图不相符。
right join
(SELECT `f_account`,`f_server_id`,`gf_account`,`gf_server_id` from `yx_hand_player_type` WHERE `type`!='1') b ---------------------------------------
主要的问题要看你这里的a和b这2个表的记录多不多,因为这里面会生成中间表的,涉及到磁盘IO的话,那当然就慢啦
建议把处理逻辑修改
select count(type_id) as promotion_count,account,server_id from ( (select * from yx_hand_player_type where (account,server_id)=(f_account,f_server_id) or (account,server_id)=(gf_account,gf_server_id)) aa ) where aa.typeid='1' group by account,server_id order by promotion_count desc limit 0,10;
select count(type_id) as promotion_count,account,server_id from ( (select * from yx_hand_player_type where (account,server_id)=(f_account,f_server_id) or (account,server_id)=(gf_account,gf_server_id)) aa ) where aa.type_id='1' group by account,server_id order by promotion_count desc limit 0,10;