一张用户表,一张订单表想知道每个用户这两年来发生多少订单,我的SQL如下:
select account_number,(select count(order_id) from jj_order where order_accountid=account_id and order_addtime<='2011-01-01' and order_addtime>'2009-01-01') as frequency from jj_account
但由于这两张表都数据超多,所以运行起来 好慢好慢~~~ 求高人指路
select account_number,(select count(order_id) from jj_order where order_accountid=account_id and order_addtime<='2011-01-01' and order_addtime>'2009-01-01') as frequency from jj_account
但由于这两张表都数据超多,所以运行起来 好慢好慢~~~ 求高人指路
show index from jj_order;
show index from jj_account;
explain select account_number,
(select count(order_id) from jj_order where order_accountid=account_id and order_addtime<='2011-01-01' and order_addtime>'2009-01-01') as frequency
from jj_account;
count(*) AS frequency
FROM jj_account a, jj_order o
WHERE a.account_id = o.order_accountId
GROUP BY a.account_id
HAVING order_addtime <= '2011-01-01' AND order_addtime > '2009-01-01'
(select count(order_id) from jj_order where order_accountid=account_id and order_addtime<='2011-01-01' and order_addtime>'2009-01-01') as frequency
from jj_account;看这个,如果有全表扫描的,建索引。
from jj_account ja left join jj_order jo on ja.account_id =jo.order_accountid
where jo.order_addtime between '2011-01-01' and '2009-01-01'
group by ja.account_number在ja.account_id jo.order_accountid jo.order_addtime ja.account_number都可以适当加上索引