select status,count(*) from articles where id in(select articleid from monitor_old where checkflag=100) group by status;其中select articleid from monitor_old where checkflag=100有13890个结果,此语句改怎么优化?
select articles.status,count(*) from articles inner join (select distinct articleid from monitor_old where checkflag=100) b on articles.id=b.articleid group by articles.status
这样写 select a.status,count(*) from articles a join monitor_old m on a.id=m.articleid where m.checkflag=100 group by a.status;与你的差不多吧?有没有效率上的区别?
EXPLAIN SQL语句,看看结果 select a.status,count(*) from articles a inner join monitor_old m on a.id=m.articleid where m.checkflag=100 group by a.status;
如果articles列上有索引的话 二楼count(*)可以改成count(articles)
查询语句中 如果in条件里的记录数多的话 效率就低了吧?就不应该用in了 对不?
mysql> explain select articles.status,count(*) from articles inner join (select distinct articleid from monitor_old where checkflag=100) b on articles.id=b.articleid group by articles.status\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 13969 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: PRIMARY table: articles type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: b.articleid rows: 1 Extra: Using where *************************** 3. row *************************** id: 2 select_type: DERIVED table: monitor_old type: ref possible_keys: checkflag key: checkflag key_len: 4 ref: rows: 15078 Extra: Using where; Using temporary 3 rows in set (0.18 sec)mysql> explain select a.status,count(*) from articles a join monitor_old m on a.id=m.articleid where m.checkflag=100 group by a.status\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: m type: ref possible_keys: checkflag key: checkflag key_len: 4 ref: const rows: 15078 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: literature.m.articleid rows: 1 Extra: Using where 2 rows in set (0.00 sec)mysql> 如何取舍?麻烦分析一下
如果 select articleid from monitor_old where checkflag=100 这个没有重复项,则显然你的第二种比较好 select a.status,count(*) from articles a join monitor_old m on a.id=m.articleid where m.checkflag=100 group by a.status;
from articles inner join (select distinct articleid from monitor_old where checkflag=100) b
on articles.id=b.articleid
group by articles.status
select a.status,count(*) from articles a join monitor_old m on a.id=m.articleid where m.checkflag=100 group by a.status;与你的差不多吧?有没有效率上的区别?
select a.status,count(*) from articles a inner join monitor_old m on a.id=m.articleid where m.checkflag=100 group by a.status;
二楼count(*)可以改成count(articles)
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 13969
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: articles
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: b.articleid
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: monitor_old
type: ref
possible_keys: checkflag
key: checkflag
key_len: 4
ref:
rows: 15078
Extra: Using where; Using temporary
3 rows in set (0.18 sec)mysql> explain select a.status,count(*) from articles a join monitor_old m on a.id=m.articleid where m.checkflag=100 group by a.status\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: checkflag
key: checkflag
key_len: 4
ref: const
rows: 15078
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: literature.m.articleid
rows: 1
Extra: Using where
2 rows in set (0.00 sec)mysql>
如何取舍?麻烦分析一下
select a.status,count(*) from articles a join monitor_old m on a.id=m.articleid where m.checkflag=100 group by a.status;
ACMAIN_CHM的分析