sql 两个表相交，如何做优化

Question

mysql 两个表相交，但是查询出现 Sending data  超时，导致PHP等待超时create table cwl_site(
siteid int(11) not null auto_increment,
primary key (siteid)
) engine=innodb;create table cwl_flink (
id int(11) not null auto_increment,
fid int(11) not null,
tid int(11) not null,
primary key(id),
key fid (fid,tid)
) engine=innodb ;
explain
select s.*,count(c.tid) num_in,(select count(fid) from cwl_flink b where b.fid=s.siteid) num_out
from cwl_site s 
left join cwl_flink c on s.siteid=c.tid
group by s.siteid
order by num_in desc limit 30
+------+--------------------+-------+-------+---------------+------+---------+------+--------+--------------------------------------------------------------+
| id   | select_type        | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                                                        |
+------+--------------------+-------+-------+---------------+------+---------+------+--------+--------------------------------------------------------------+
|    1 | PRIMARY            | s     | ALL   | NULL          | NULL | NULL    | NULL | 207884 | Using temporary; Using filesort                              |
|    1 | PRIMARY            | c     | index | NULL          | fid  | 8       | NULL | 393171 | Using where; Using index; Using join buffer (flat, BNL join) |
|    2 | DEPENDENT SUBQUERY | b     | ref   | fid           | fid  | 4       | func |      6 | Using index                                                  |
+------+--------------------+-------+-------+---------------+------+---------+------+--------+--------------------------------------------------------------+
3 rows in set (0.00 sec)
SQL在 表中执行查询无结果，可否有优化境地。

Answer 1

create index xxxx on cwl_flink(tid);

Answer 2

+------+--------------------+-------+-------+---------------+---------+---------+----------------+--------+---------------------------------+
| id   | select_type        | table | type  | possible_keys | key     | key_len | ref            | rows   | Extra                           |
+------+--------------------+-------+-------+---------------+---------+---------+----------------+--------+---------------------------------+
|    1 | PRIMARY            | s     | index | NULL          | PRIMARY | 4       | NULL           | 295372 | Using temporary; Using filesort |
|    1 | PRIMARY            | c     | ref   | tid           | tid     | 4       | crawl.s.siteid |      1 | Using index                     |
|    2 | DEPENDENT SUBQUERY | b     | ref   | fid           | fid     | 4       | func           |      7 | Using index                     |
+------+--------------------+-------+-------+---------------+---------+---------+----------------+--------+---------------------------------+
3 rows in set (0.00 sec)
TKS

Answer 3

不过发现一个问题，执行花费了 14秒多。然后PHP等待就超时了。nginx就报 502MariaDB [crawl]> 
select s.*,count(b.tid) num_in, (select count(fid) from cwl_flink a where a.fid=s.siteid )  num_out  from cwl_site s  left join cwl_flink b on s.siteid=b.tid  group by b.tid
order by num_in desc limit  0,1;
+--------+----------------------+------------------+-----------+-----------------------------+---------+-----------+--------+----------+-------+----------+------------+------------+--------+--------+---------+
| siteid | hostname             | tophost          | hostlevel | indexurl                    | charset | metatitle | metakw | metadesc | alexa | alexaext | addtime    | uptime     | sortid |
 num_in | num_out |
+--------+----------------------+------------------+-----------+-----------------------------+---------+-----------+--------+----------+-------+----------+------------+------------+--------+--------+---------+
|   7931 | www.miitbeian.gov.cn | miitbeian.gov.cn |         1 | http://www.miitbeian.gov.cn |         |           |        |          | 21133 | CN:China | 1481889887 | 1488360664 |    100 |
   4781 |       0 |
+--------+----------------------+------------------+-----------+-----------------------------+---------+-----------+--------+----------+-------+----------+------------+------------+--------+--------+---------+
1 row in set (14.34 sec)
可否还有优化的余地呢
MariaDB [crawl]> explain select s.*,count(b.tid) num_in, (select count(fid) from cwl_flink a where a.fid=s.siteid )  num_out  from cwl_site s  left join cwl_flink b on s.siteid=b.tid  group 
by b.tid order by num_in desc limit  0,1;
+------+--------------------+-------+------+---------------+------+---------+----------------+--------+---------------------------------+
| id   | select_type        | table | type | possible_keys | key  | key_len | ref            | rows   | Extra                           |
+------+--------------------+-------+------+---------------+------+---------+----------------+--------+---------------------------------+
|    1 | PRIMARY            | s     | ALL  | NULL          | NULL | NULL    | NULL           | 296497 | Using temporary; Using filesort |
|    1 | PRIMARY            | b     | ref  | tid           | tid  | 4       | crawl.s.siteid |      1 | Using index                     |
|    2 | DEPENDENT SUBQUERY | a     | ref  | fid           | fid  | 4       | func           |      5 | Using index                     |
+------+--------------------+-------+------+---------------+------+---------+----------------+--------+---------------------------------+
3 rows in set (0.00 sec)

Answer 4

建议语句在逻辑上进行重新分析修改。无法理解为什么使用 a left join b 然后又使用 group by b.id,再 order by b..tid desc limit 1， 这样左连接的意图是什么？

Answer 5

表 cwl_flink 存放的是 友情链接关系表。交表查询为了得到格式为 
1.网站ID $id
2.从这个网站连出去的友情链接数目，等同于执行 select count(1) from cwl_flink where fid=$id
3.从其他网站连接到这个网站的友情链接数据 ，等同于 select count(1) from cwl_flink where  tid=$id为了节省查询，所以想到在left join 上做group by