SQL占用时间太长，请帮忙调优

Question

有一个SQL总共耗时21秒，占用时间太长，哪位能帮忙优化一下。select DATE (a.starttime) as duration,
          count(aa.userid) as uni,
          sum(aa.login_total) as noneuni,
          sum(aa.gender = 1) as male,
          sum(aa.gender = 2) as female
   from activitylog a,
        (select COUNT(c.userid) as login_total, c.userid, u.gender from activitylog c, hiv2.user u
         where c.subtype = 'LOGOUT' and c.starttime >= '2010-03-31' and c.starttime <=
          '2010-06-29 23:59:59' and c.userid = u.userid and u.createtime < '2010-06-29' group by
           c.userid) as aa
   where a.userid = aa.userid and
         a.subtype = 'LOGOUT' and
         a.starttime >= '2010-03-31' and
         a.starttime <= '2010-06-29 23:59:59' and
         aa.login_total < 2
   group by duration
a. SQL意图：统计2010-03-31到2010-06-29 23:59:59之间，注册日期在2010-06-29（u.createtime < '2010-06-29'）之前，登陆次数少于2次的（aa.login_total < 2）
的玩家 的登陆情况。这个SQL是组合查询，动态生成。b. 详细说明：DATE (a.starttime) as duration -----是根据用户选择（按天DATE (a.starttime)、按周week (a.starttime)、按月month(a.starttime)）查询
count(aa.userid) as uni-----是统计总共有多少玩家
sum(aa.login_total) as noneuni-----是统计这些玩家共登陆了多少次
sum(aa.gender = 1) as male-----统计男玩家
 sum(aa.gender = 2) as female----统计女玩家
 （注：玩家和用户是两个概念）c. 数据说明：
 1.表activitylog a是一个记录玩家动作的表，里面有LOGOUT，还有其它的数据，现在大约有35W 数据，已经建了部分索引了（id,starttime,endtime,userid,共4个独立的索引），表结构如下： mysql> desc activitylog;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment | 
| type       | varchar(20)         | NO   |     | NULL    |                | 
| subtype    | varchar(20)         | NO   |     | NULL    |                | 
| starttime  | datetime            | YES  | MUL | NULL    |                | 
| endtime    | datetime            | YES  | MUL | NULL    |                | 
| hostname   | varchar(40)         | YES  |     | NULL    |                | 
| userid     | bigint(20) unsigned | YES  | MUL | NULL    |                | 
| patchid    | varchar(40)         | YES  |     | NULL    |                | 
| escapegame | int(11)             | YES  |     | NULL    |                | 
| exp_before | int(11)             | YES  |     | NULL    |                | 
| exp_gain   | int(11)             | YES  |     | NULL    |                | 
| exp_after  | int(11)             | YES  |     | NULL    |                | 
+------------+---------------------+------+-----+---------+----------------+2.表 hiv2.user u是在hiv2库中的user表，里面存放玩家信息，大约有20W数据，已经建了部分索引了（userid,username,planetid,country,createtime共5个独立的索引），表结构如下：
mysql> desc hiv2.user;
+-------------------------+---------------------+------+-----+--------------------------------------+-------+
| Field                   | Type                | Null | Key | Default                              | Extra |
+-------------------------+---------------------+------+-----+--------------------------------------+-------+
| userid                  | bigint(20) unsigned | NO   | PRI | NULL                                 |       | 
| username                | char(50)            | YES  | MUL | NULL                                 |       | 
| nickname                | varchar(100)        | YES  |     | NULL                                 |       | 
| passwd                  | varchar(128)        | YES  |     | NULL                                 |       | 
| state                   | int(11)             | YES  |     | 0                                    |       | 
| disabletime             | datetime            | YES  |     | NULL                                 |       | 
| email                   | varchar(100)        | YES  |     | NULL                                 |       | 
| avatarurl               | varchar(100)        | YES  |     | e8030000-0000-0000-0000-000000000000 |       | 
| planetid                | bigint(20) unsigned | YES  | MUL | NULL                                 |       | 
| last_login_planetid     | bigint(20)          | YES  |     | NULL                                 |       | 
| last_login_location     | varchar(50)         | YES  |     | 37 26 45                             |       | 
| usertype                | int(11)             | YES  |     | NULL                                 |       | 
| realname                | varchar(100)        | YES  |     | NULL                                 |       | 
| country                 | varchar(100)        | NO   | MUL | SG                                   |       | 
| timezone                | varchar(5)          | YES  |     | NULL                                 |       | 
| birthday                | date                | NO   |     | 1991-01-01                           |       | 
| gender                  | int(11)             | NO   |     | 1                                    |       | 
| question                | varchar(50)         | YES  |     | NULL                                 |       | 
| answer                  | varchar(50)         | YES  |     | NULL                                 |       | 
| registerip              | varchar(20)         | YES  |     | NULL                                 |       | 
| createtime              | datetime            | YES  | MUL | NULL                                 |       | 
| updatetime              | datetime            | YES  |     | NULL                                 |       | 
| total_loginnum          | int(11)             | YES  |     | NULL                                 |       | 
| total_logintime         | bigint(20)          | YES  |     | NULL                                 |       | 
| read_announcement_time  | datetime            | YES  |     | NULL                                 |       | 
| lastlogintime           | datetime            | YES  |     | NULL                                 |       | 
| kickout_recover_time    | datetime            | YES  |     | NULL                                 |       | 
| forbidtalk_recover_time | datetime            | YES  |     | NULL                                 |       | 
| basic_permission        | bigint(20) unsigned | YES  |     | NULL                                 |       | 
| constellation           | varchar(20)         | YES  |     | NULL                                 |       | 
| last_login_scene        | varchar(50)         | YES  |     | 0                                    |       | 
| introduce               | varchar(250)        | YES  |     | NULL                                 |       | 
| organisation            | varchar(250)        | YES  |     | NULL                                 |       | 
| first_name              | varchar(20)         | YES  |     | NULL                                 |       | 
| last_name               | varchar(20)         | YES  |     | NULL                                 |       | 
| promo_code              | varchar(20)         | YES  |     | NULL                                 |       | 
+-------------------------+---------------------+------+-----+--------------------------------------+-------+
36 rows in set (0.01 sec)

Answer 1

d. SQL性能分析1. mysql> explain   select DATE (a.starttime) as duration,
    ->           count(aa.userid) as uni,
    ->           sum(aa.login_total) as noneuni,
    ->           sum(aa.gender = 1) as male,
    ->           sum(aa.gender = 2) as female
    ->    from activitylog a,
    ->         (select COUNT(c.userid) as login_total, c.userid, u.gender from activitylog c, hiv2.user u
    ->          where c.subtype = 'LOGOUT' and c.starttime >= '2010-03-31' and c.starttime <=
    ->           '2010-06-29 23:59:59' and c.userid = u.userid and u.createtime < '2010-06-29' group by
    ->            c.userid) as aa
    ->    where a.userid = aa.userid and
    ->          a.subtype = 'LOGOUT' and
    ->          a.starttime >= '2010-03-31' and
    ->          a.starttime <= '2010-06-29 23:59:59' and
    ->          aa.login_total < 2
    ->    group by duration;
+----+-------------+------------+--------+----------------------+---------+---------+-----------------+--------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys        | key     | key_len | ref             | rows   | Extra                                        |
+----+-------------+------------+--------+----------------------+---------+---------+-----------------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                 | NULL    | NULL    | NULL            |  47617 | Using where; Using temporary; Using filesort | 
|  1 | PRIMARY     | a          | ref    | userid,start_index   | userid  | 9       | aa.userid       |      3 | Using where                                  | 
|  2 | DERIVED     | c          | ALL    | userid,start_index   | NULL    | NULL    | NULL            | 365979 | Using where; Using temporary; Using filesort | 
|  2 | DERIVED     | u          | eq_ref | PRIMARY,create_index | PRIMARY | 8       | yogmgt.c.userid |      1 | Using where                                  | 
+----+-------------+------------+--------+----------------------+---------+---------+-----------------+--------+----------------------------------------------+
4 rows in set (7.12 sec)
2.  mysql> show profile for query 2;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000025 |
| checking query cache for query |  0.000167 |
| checking permissions           |  0.000004 |
| checking permissions           |  0.000002 |
| checking permissions           |  0.000009 |
| Opening tables                 |  0.000025 |
| System lock                    |  0.000006 |
| Table lock                     |  0.000114 |
| optimizing                     |  0.000024 |
| statistics                     |  0.000518 |
| preparing                      |  0.000028 |
| Creating tmp table             |  0.000025 |
| executing                      |  0.000003 |
| Copying to tmp table           |  7.036612 |
| Sorting result                 |  0.017781 |
| Sending data                   |  0.018868 |
| removing tmp table             |  0.000014 |
| Sending data                   |  0.000069 |
| init                           |  0.000066 |
| optimizing                     |  0.000024 |
| statistics                     |  0.000375 |
| preparing                      |  0.000028 |
| Creating tmp table             |  0.000052 |
| executing                      |  0.000003 |
| Copying to tmp table           | 14.861155 |
| Sorting result                 |  0.000059 |
| Sending data                   |  0.000151 |
| end                            |  0.000003 |
| removing tmp table             |  0.000009 |
| end                            |  0.000004 |
| query end                      |  0.000004 |
| freeing items                  |  0.000226 |
| storing result in query cache  |  0.000429 |
| removing tmp table             |  0.000142 |
| closing tables                 |  0.000007 |
| logging slow query             |  0.000003 |
| logging slow query             |  0.000002 |
| cleaning up                    |  0.000005 |
+--------------------------------+-----------+
38 rows in set (0.00 sec)| Copying to tmp table    7.036612 |，| Copying to tmp table  14.861155 |执行了两次Copying to tmp table，是整个SQL中最耗时的地方，也是问题的所在。
网上查了一些资料，解决这个问题，设置my.cnf里两个参数  tmp_table_size=200M，max_heap_table_size=64M但是发现好像作用不明显，设置 前占用21秒，设置后也占用21秒，不知道问题的所在，
请大家指点一下如何解决这个问题。
e: mysql配置，mysql服务器是linux系统my.cnf文件参数如下：[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set=utf8[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0[mysqld]tmp_table_size=200M
max_heap_table_size=64M
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-lockingbind-address            = 0.0.0.0所有信息我都贴出来了，望大家指点一下。帮忙改进一下SQL也行，帮忙解决Copying to tmp table耗时较多的问题也行，不胜感激。
另外，还有一个帖子已成垃圾贴,没人回复，谁想接分就去回复一下，立马结贴。（http://topic.csdn.net/u/20100622/12/b6ff806d-4575-4674-83d2-5c630e0561a3.html）

Answer 2

select DATE (a.starttime) as duration,
          count(u.userid) as uni,
          sum(a.userid) as noneuni,
          sum(u.gender = 1) as male,
          sum(u.gender = 2) as female
   from activitylog a,hiv2.user u
   where a.userid = u.userid and
         a.subtype = 'LOGOUT' and
         a.starttime >= '2010-03-31' and
         a.starttime <= '2010-06-29 23:59:59'
   group by duration 
 90 rows in set (7.22 sec)
这样才用7sec，所以问题主要出在login_total上，怎样才能解决这个login_total?另外，单独执行select COUNT(c.userid) as login_total, c.userid, u.gender from activitylog c, hiv2.user u
         where c.subtype = 'LOGOUT' and c.starttime >= '2010-03-31' and c.starttime <=
          '2010-06-29 23:59:59' and c.userid = u.userid and u.createtime < '2010-06-29' group by        c.userid47617 rows in set (7.10 sec)，也耗时7sec，不过如果两个联合起来就21sec,怎样才能一次就把login_total解决？

Answer 3

能否先把select COUNT(c.userid) as login_total, c.userid, u.gender from activitylog c, hiv2.user u
  where c.subtype = 'LOGOUT' and c.starttime >= '2010-03-31' and c.starttime <=
  '2010-06-29 23:59:59' and c.userid = u.userid and u.createtime < '2010-06-29' group by c.userid 查询结果放到一个临时表中，再做连接，试一试效果怎么样？
  另外有个小疑问，为什么select DATE (a.starttime) as duration,
  count(u.userid) as uni,
  sum(a.userid) as noneuni,
  sum(u.gender = 1) as male,
  sum(u.gender = 2) as female
  from activitylog a,hiv2.user u
  where a.userid = u.userid and
  a.subtype = 'LOGOUT' and
  a.starttime >= '2010-03-31' and
  a.starttime <= '2010-06-29 23:59:59'
  group by duration  
可以实现，你却要用最上面的查询语句？

Answer 4

问题解决了，在user表里加个了字段，专门记录login_total字段。
修改后整个查询占用约7秒时间。 另外有个小疑问，为什么select DATE (a.starttime) as duration,
  count(u.userid) as uni,
  sum(a.userid) as noneuni,
  sum(u.gender = 1) as male,
  sum(u.gender = 2) as female
  from activitylog a,hiv2.user u
  where a.userid = u.userid and
  a.subtype = 'LOGOUT' and
  a.starttime >= '2010-03-31' and
  a.starttime <= '2010-06-29 23:59:59'
  group by duration   
可以实现，你却要用最上面的查询语句这个是实现不了功能的，它没有统计登陆次数，我只是把整个SQL拆开看看各段执行时间。