mysql两个大表数据的查询。

[床上等您]

有一个基本表：tba 大概100w左右数据。
结构：
kid BIGINT(20) 非空，无符号，主键
cid MEDIUMINT(7)
status

有很多个字段，只用到这三个。
这个表的索引：
kid
cid + updatetime(这个也是时间戳)
cid + aid(这是个其它表的id)

========

另外一个表：tbb，大概有5KW左右，还会继续增长
结构：
statsid 自增id，主键
id 对应tba表的kid，非唯一。
cid 与tba表的cid对应
createtime 时间戳，保存每天的零时。
clicks int 点击数，即一个id每一天的点击数
costs float 消费，与clicks一样。

索引：
主键id
id + createtime
cid + createtime


这个表也有很多个字段，目前只用到这几个。

=================

现在说说实现的功能：
想统计tba表里 每个数据的某段时间（假设为半个月）时的点击数

目前的方法：select xxxx,sum(clicks) from tba left join tbb (a.kid=b.id and createtime between 半个月时间) group by b.id


还有一种方法：
把b表的数据拿出来，在程序里计算。不过效率也很低。


请教大侠们sql优化。。

[wwwwb]

select A.KID,sum(clicks) from tba A left join tbb
ON (a.kid=b.id and createtime between 半个月时间) group by A.Kid
EXPLAIN SQL语句，要tba中的每个，只有LEFT JOIN

[床上等您]

[Quote=引用 1 楼 的回复:]

select kid,(select sum(clicks) from tbb where id=tba.kid and createtime between 半个月时间)
from tba
[/Quote]
这个sql大概花了2秒多时间

SELECT sql_no_cache SUM(s.clicks) clicks from tba l LEFT JOIN tbb s ON s.id = l.kid where l.status <9 AND s.cid = 2776 AND s.createtime >= 1350057600 AND s.createtime <= 1351408586 GROUP BY s.id ORDER BY clicks DESC

这个sql大概花了1.3秒左右。我己经强制不读缓存了。
这个sql能否再优化一下？或者还有更优解？？

[床上等您]

急，在线等。。。。上头催着啊。。。

[wwwwb]

EXPLAIN SQL语句，贴结果，去掉ORDER BY，速度如何

[wwwwb]

status有几种值

[床上等您]

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	s	range	cid	cid	7		299300	Using where
2	DEPENDENT SUBQUERY	keyword_setting	unique_subquery	PRIMARY,cid,idx_cid_updatetime	PRIMARY	8	func	1	Using where

[床上等您]

[Quote=引用 6 楼 的回复:]

status有几种值
[/Quote]
3种。

[wwwwb]

去掉ORDER BY，速度如何

status有3种值，索引用处不大

[床上等您]

上面的explain 没有去掉order，格式化不好。
这个是没有order的。

+----+-------------+-------+--------+---------------+---------+---------+----------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows   | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+----------------+--------+----------------------------------------------+
|  1 | SIMPLE      | s     | range  | id,cid        | cid     | 7       | NULL           | 299300 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY       | PRIMARY | 8       | sempstats.s.id |      1 | Using where                                  |
+----+-------------+-------+--------+---------------+---------+---------+----------------+--------+----------------------------------------------+

[床上等您]

[Quote=引用 9 楼 的回复:]

去掉ORDER BY，速度如何

status有3种值，索引用处不大
[/Quote]
用上这个索引是作用不大。
去掉order后，时间相基本一样

[wwwwb]

SELECT sql_no_cache SUM(s.clicks) clicks from tba l LEFT JOIN tbb s ON s.id = l.kid where l.status <9 AND s.cid = 2776 AND s.createtime >= 1350057600 AND s.createtime <= 1351408586 GROUP BY s.id ORDER BY clicks DESC
从EXPLAIN的信息来看，索引已经用上

试试
SELECT sql_no_cache SUM(s.clicks) clicks from
(select * from tba where createtime >= 1350057600 AND createtime <= 1351408586) l LEFT JOIN tbb s ON s.id = l.kid where l.status <9 AND s.cid = 2776 AND GROUP BY s.id ORDER BY clicks DESC

[床上等您]

[Quote=引用 12 楼 的回复:]

SELECT sql_no_cache SUM(s.clicks) clicks from tba l LEFT JOIN tbb s ON s.id = l.kid where l.status <9 AND s.cid = 2776 AND s.createtime >= 1350057600 AND s.createtime <= 1351408586 GROUP BY s.id ORDE……
[/Quote]
试了，相差不多，还慢了0.1-0.2秒左右。。。

[wwwwb]

在tbb 上建立
id、cid、createtime索引，去掉
id + createtime
cid + createtime
这2个索引 OR 强制使用id、cid、createtime索引

[ACMAIN_CHM]

select kid,(select sum(clicks) from tbb where id=tba.kid and createtime between 半个月时间)
from tba