mysql的查询好难_【讨论】复杂SQL查询是否很难优化了？

今天在一个子产品中监控发现SQL如下

SELECT COUNT(DISTINCT CASE

WHEN isnew = 1 THEN

visitorid

END) AS sumnuv,

COUNT(*) AS sumpv,

COUNT(DISTINCT visitorid) AS sumuv,

COUNT(DISTINCT visitid) AS sumvt,

COUNT(DISTINCT ip_addr) AS sumip,

ROUND(SUM(stime) / COUNT(DISTINCT visitid)) AS avgstime,

ROUND(SUM(atime) / COUNT(DISTINCT visitid)) AS avgatime,

ROUND(SUM(ltime) / COUNT(DISTINCT visitid)) AS avgltime,

ROUND(COUNT(CASE

WHEN entry_flag = 1 AND exit_flag = 1 THEN

1

END) / COUNT(DISTINCT visitid) * 100,

2) AS jumprate1,

ROUND(COUNT(CASE

WHEN entry_flag = 1 AND exit_flag = 1 AND atime <= 5 THEN

1

END) / COUNT(DISTINCT visitid) * 100,

2) AS jumprate2,

IFNULL(SUM(tf_times), 0) AS sumtf

FROM as_pv_20110523

WHERE siteid = 20381

表as_pv_20110523  记录数 809213

执行计划

+----+-------------+----------------+------+---------------+------------+---------+-------+--------+-------+

| id | select_type | table          | type | possible_keys | key        | key_len | ref   | rows   | Extra |

+----+-------------+----------------+------+---------------+------------+---------+-------+--------+-------+

|  1 | SIMPLE      | as_pv_20110523 | ref  | idx_siteid    | idx_siteid | 4       | const | 303182 |       |

+----+-------------+----------------+------+---------------+------------+---------+-------+--------+-------+

然后执行了下

+--------+--------+--------+--------+--------+----------+----------+----------+-----------+-----------+-------+

| sumnuv | sumpv  | sumuv  | sumvt  | sumip  | avgstime | avgatime | avgltime | jumprate1 | jumprate2 | sumtf |

+--------+--------+--------+--------+--------+----------+----------+----------+-----------+-----------+-------+

| 424475 | 760279 | 627924 | 714474 | 540253 |       39 |       12 |      100 |     95.14 |     56.30 |     0 |

+--------+--------+--------+--------+--------+----------+----------+----------+-----------+-----------+-------+

1 row in set (3 min 19.27 sec)

居然要这么长时间

我把一些主要的参数稍微调整了下，但是速度还是比较慢

1、我知道复杂查询在MySQL中开销是硬开销，是不是很难优化？

2、一些my.cnf中的主要和相关参数我都已经调整过了，怎么还是这么慢

怎么提高这个查询的速度呢？