速度果然慢爆了~求优化

Question

优化sql

Answer 1

（表一）photo_table：（相册表）
photo_id int
user_id   int
images_url   varchar
（表二）user_attention：（关注表）
gz_id   int
user_id  int 
guanzhu_id
（表三）
account：（用户表）
user_id  int
user_name   varchar效果是：
统计   每个用户  有多少张照片，和多少个关注人。 

Answer 2

group by +order by 肯定会慢呀

Answer 3

哦哦  数据量的话   也就1w条数据量，他们都有 user_id  作为主外键，mysql   在哪里能看到执行计划哦?

Answer 4

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL (NULL) (NULL) (NULL) (NULL) 12351 Using where; Using temporary; Using filesort
1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 11830  
1 PRIMARY <derived3> ALL (NULL) (NULL) (NULL) (NULL) 1522  
1 PRIMARY <derived4> ALL (NULL) (NULL) (NULL) (NULL) 1294  
4 DERIVED fs ALL user_id (NULL) (NULL) (NULL) 3360 Using temporary; Using filesort
4 DERIVED a eq_ref PRIMARY PRIMARY 4 meet_com.fs.user_id 1 Using index
3 DERIVED lt index receive_id receive_id 4 (NULL) 9522 Using index; Using temporary; Using filesort
3 DERIVED a eq_ref PRIMARY PRIMARY 4 meet_com.lt.receive_id 1 Using index
2 DERIVED a index PRIMARY PRIMARY 4 (NULL) 12351 Using index
2 DERIVED p ref user_id user_id 4 meet_com.a.user_id 1 Using index

Answer 5

数据库 差不多的吧？
<table border=1>
<tr>
<td bgcolor=silver class='medium'>id</td><td bgcolor=silver class='medium'>select_type</td><td bgcolor=silver class='medium'>table</td><td bgcolor=silver class='medium'>type</td><td bgcolor=silver class='medium'>possible_keys</td><td bgcolor=silver class='medium'>key</td><td bgcolor=silver class='medium'>key_len</td><td bgcolor=silver class='medium'>ref</td><td bgcolor=silver class='medium'>rows</td><td bgcolor=silver class='medium'>Extra</td></tr>
<tr>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>a</td>
<td class='normal' valign='top'>ALL</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>12351</td>
<td class='normal' valign='top'>Using where; Using temporary; Using filesort</td>
</tr>
<tr>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>&lt;derived2&gt;</td>
<td class='normal' valign='top'>ALL</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>11830</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>&lt;derived3&gt;</td>
<td class='normal' valign='top'>ALL</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>1522</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>&lt;derived4&gt;</td>
<td class='normal' valign='top'>ALL</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>1294</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>DERIVED</td>
<td class='normal' valign='top'>fs</td>
<td class='normal' valign='top'>ALL</td>
<td class='normal' valign='top'>user_id</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>3360</td>
<td class='normal' valign='top'>Using temporary; Using filesort</td>
</tr>
<tr>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>DERIVED</td>
<td class='normal' valign='top'>a</td>
<td class='normal' valign='top'>eq_ref</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>meet_com.fs.user_id</td>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>Using index</td>
</tr>
<tr>
<td class='normal' valign='top'>3</td>
<td class='normal' valign='top'>DERIVED</td>
<td class='normal' valign='top'>lt</td>
<td class='normal' valign='top'>index</td>
<td class='normal' valign='top'>receive_id</td>
<td class='normal' valign='top'>receive_id</td>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>9522</td>
<td class='normal' valign='top'>Using index; Using temporary; Using filesort</td>
</tr>
<tr>
<td class='normal' valign='top'>3</td>
<td class='normal' valign='top'>DERIVED</td>
<td class='normal' valign='top'>a</td>
<td class='normal' valign='top'>eq_ref</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>meet_com.lt.receive_id</td>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>Using index</td>
</tr>
<tr>
<td class='normal' valign='top'>2</td>
<td class='normal' valign='top'>DERIVED</td>
<td class='normal' valign='top'>a</td>
<td class='normal' valign='top'>index</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>12351</td>
<td class='normal' valign='top'>Using index</td>
</tr>
<tr>
<td class='normal' valign='top'>2</td>
<td class='normal' valign='top'>DERIVED</td>
<td class='normal' valign='top'>p</td>
<td class='normal' valign='top'>ref</td>
<td class='normal' valign='top'>user_id</td>
<td class='normal' valign='top'>user_id</td>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>meet_com.a.user_id</td>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>Using index</td>
</tr>
</table>

Answer 6

select  a.user_id
       ,count(b.user_id) photo_num
       ,count(c.user_id) fensi_num
from account a
left join photo_table b on a.user_id =b.user_id 
left join user_attention c on a.user_id =c.user_id 
group by a.user_id