求助一个SQL查询问题

Question

表1：ts_relog（用户注册表）
字段：actuserid,provinceid,cityid,actuserrole,actusertype,opdate
表2：ts_logindetail（活跃用户表）
字段：actuserid,provinceid,cityid,actuserrole,actusertype,opdate现在我要查的是沉默30天以上60天以下的用户数（可添加查询条件，包括actuserrole,actusertype，按provinceid,cityid分组）下面是我的查询语句：
SELECT COUNT(DISTINCT actuserid),provinceid,cityid
FROM (SELECT DISTINCT actuserid,provinceid,cityid,actuserrole,actusertype,opdate FROM ts_reglog WHERE opdate>='2010-10-06' AND opdate<='2010-11-06' AND actuserrole='4' AND actusertype='1') AS tmp 
WHERE NOT EXISTS(SELECT DISTINCT actuserid FROM ts_logindetail WHERE actuserid = tmp.actuserid AND opdate>='2010-10-06' AND opdate<='2010-12-05' AND actuserrole='4' AND actusertype='1')
GROUP BY provinceid,cityid查询非常的慢，基本等不到结果，还请高手赐教

Answer 1

贴插入记录的SQL语句、正确结果贴出来

Answer 2

好的这个是ts_reglog的5条记录（部分）
actuserid, actmobile,   actuserrole, actusertype,opdate,   provinceid,cityid,
16489880,  13947989396, 1,           1,          1970-1-1, 471,       3106,
16487545,  13514798721, 1,           1,          1970-1-1, 471,       3106,
1000620,   13999999992, 1,           1,          2009-1-16,991,       3200,
1001360,   13854167007, 2,           1,          2009-1-20,531,       1714,这个是ts_logindetail的5条记录（部分）actuserid, actmobile,   actuserrole, actusertype, provinceid, cityid, opdate
101057108, 13911888387, 2,           5,           998,        3447,   2010-8-30
101057112, 13602533884, 2,           5,           998,        3437,   2010-8-30
101054373, 13925204498, 2,           5,           998,        3437,   2010-8-30
101074168, 13924614998, 2,           5,           998,        3442,   2010-8-30
101057037, 13713960225, 2,           5,           997,        3454,   2010-8-30这个是我把查询时间缩短后的部分查询结果：count(distinct actuserid), provinceid, cityid
1342,                      0,          0
3,                         100,        115
1,                         100,        137
1,                         200,        1180
1,                         230,        2148

Answer 3

LZ要养成写好Create table ... insert ...的好习惯，这样问问题的效率就会高很多。

Answer 4

贴出你的以下结果，以供分析。show index from ts_relog;
show index from ts_logindetail;
explain SELECT COUNT(DISTINCT actuserid),provinceid,cityid
FROM (SELECT DISTINCT actuserid,provinceid,cityid,actuserrole,actusertype,opdate FROM ts_reglog WHERE opdate>='2010-10-06' AND opdate<='2010-11-06' AND actuserrole='4' AND actusertype='1') AS tmp  
WHERE NOT EXISTS(SELECT DISTINCT actuserid FROM ts_logindetail WHERE actuserid = tmp.actuserid AND opdate>='2010-10-06' AND opdate<='2010-12-05' AND actuserrole='4' AND actusertype='1')
GROUP BY provinceid,cityid;

Answer 5

show index from ts_relog;insert into `STATISTICS` 
(`Table`, `Non_unique`, `Key_name`, `Seq_in_index`, `Column_name`, `Collation`, `Cardinality`, `Sub_part`, `Packed`, `Null`, `Index_type`, `Comment`)
 values
('ts_reglog','1','id','1','actuserid','A','11689367',NULL,NULL,'YES','BTREE','');
show index from ts_logindetail;insert into `STATISTICS` (`Table`, `Non_unique`, `Key_name`, `Seq_in_index`, `Column_name`, `Collation`, `Cardinality`, `Sub_part`, `Packed`, `Null`, `Index_type`, `Comment`) 
values('ts_logindetail','1','prov','1','provinceid','A','45',NULL,NULL,'YES','BTREE','');

Answer 6

楼主请按如下方式提供你的信息。
mysql> show index from wsz1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| wsz1  |          0 | id       |            1 | id          | A         |           6 |     NULL | NULL   |      | BTREE      |         |
| wsz1  |          1 | time     |            1 | time        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| wsz1  |          1 | gid      |            1 | gid         | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.06 sec)mysql> explain select * from test;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.05 sec)mysql>

Answer 7

EXPLAIN:
1 PRIMARY <derived2> ALL \N \N \N \N 1440 Using where; Using filesort
3 DEPENDENT SUBQUERY ts_logindetail ALL \N \N \N \N 2893927 Using where; Using temporary
2 DERIVED ts_reglog ALL \N \N \N \N 11689520 Using where; Using temporary

Answer 8

唉。新手。你还是先改成下面这个试试吧。SELECT COUNT(DISTINCT actuserid),provinceid,cityid
FROM ts_reglog tmp
WHERE opdate>='2010-10-06' AND opdate<='2010-11-06' 
AND actuserrole='4' 
AND actusertype='1'
WHERE NOT EXISTS(
SELECT 1 
FROM ts_logindetail 
WHERE actuserid = tmp.actuserid 
AND opdate>='2010-10-06' AND opdate<='2010-12-05' 
AND actuserrole='4' 
AND actusertype='1')
GROUP BY provinceid,cityid