mysql子查询优化问题求助！~ 紧急！！！

[「已注销」]

先说下业务需求，实现类似问卷调查结果的功能查询，两张表
主表：visit_log 记录访问主记录
附表：ask_result_log 记录问卷结果，两表通过主表的 uniqueid字段关联。通过附表的uniqueid group y 纵转横 将 Que_ID字段拆成新的列。这个Que_ID 数据值不是固定的，根据问题的设置可能会很多。

简单的说就是 主表关联一个列数不固定的纵转横附表，现在遇到了性能问题，迫切需要优化！！~

建表语句：

CREATE TABLE `visit_log` (
  `uniqueid` varchar(20) NOT NULL,
  `call_date` datetime default NULL,
  `status` varchar(6) default NULL,
  `phone_number` varchar(18) default NULL,
  `user` varchar(20) default NULL,  
  PRIMARY KEY  (`uniqueid`),
  KEY `call_date` (`call_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
CREATE TABLE `ask_result_log` (
  `ID` int(11) NOT NULL auto_increment,
  `Phone_Number` varchar(20) default NULL,
  `Form_Value` varchar(190) default '',   
  `uniqueid` varchar(32) default NULL,   
  `Que_ID` varchar(12) default '0',
  `Call_Date` timestamp NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  KEY `uniqueid` (`uniqueid`), 
  KEY `call_date` (`Call_Date`) 
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 插入测试数据
insert into visit_log(uniqueid,call_date,status,phone_number,user)
VALUES
('1353116141.471557','2012-11-17 09:35:51','NA','2353116315','1001')
,('1353116969.473532','2012-11-17 09:49:31','B','3353116315','1001');

insert into ask_result_log(Phone_Number,Form_Value,uniqueid,Que_ID,Call_Date)
values 
('2353116315','丰田','1353116141.471557','504','2012-11-17 09:35:51')
,('2353116315','黑龙江','1353116141.471557','510','2012-11-17 09:35:51')
,('2353116315','张三','1353116141.471557','511','2012-11-17 09:35:51')
,('3353116315','大众','1353116969.473532','504','2012-11-17 09:49:31')
,('3353116315','江苏','1353116969.473532','510','2012-11-17 09:49:31')
,('3353116315','李四','1353116969.473532','511','2012-11-17 09:49:31');

-- sql语句如下

mysql> select a.phone_number,a.call_date,a.status
    -> ,datas.Q1
    -> ,datas.Q2
    -> ,datas.Q3
    -> from visit_log a inner join (
    ->
    -> select uniqueid
    -> ,max(case when que_id='504' then form_value end) as `Q1`
    -> ,max(case when que_id='510' then form_value end) as `Q2`
    -> ,max(case when que_id='511' then form_value end) as `Q3`
    -> from ask_result_log
    -> where call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59'
    -> group by uniqueid order by null
    ->
    -> )datas on datas.uniqueid=a.uniqueid
    -> where
    -> a.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59'
    ->
    -> order by a.call_date desc limit 0,15;
+--------------+---------------------+--------+------+--------+------+
| phone_number | call_date           | status | Q1   | Q2     | Q3   |
+--------------+---------------------+--------+------+--------+------+
| 3353116315   | 2012-11-17 09:49:31 | B      | 大众 | 江苏   | 李四 |
| 2353116315   | 2012-11-17 09:35:51 | NA     | 丰田 | 黑龙江 | 张三 |
+--------------+---------------------+--------+------+--------+------+
2 rows in set (0.08 sec)

-- 结果如上，ask_result_log 表产生的列数是不固定的 Q1   | Q2     | Q3   |QN...
-- 现在遇到的问题是 ask_result_log  在数据量大的时候 查询会很缓慢，很占CPU资源。想优化下这条语句，请求各位mysql高手相助啊！~

-- 执行计划如下 

[ACMAIN_CHM]

select a.phone_number,a.call_date,a.status ,max(case when b.que_id='504' then form_value end) as `Q1` ,max(case when b.que_id='510' then form_value end) as `Q2` ,max(case when b.que_id='511' then form_value end) as `Q3` from visit_log a inner join ask_result_log b on b.uniqueid=a.uniqueid where a.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59' and b.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59' group by a.uniqueid

[uu_lt]

去掉 order by null 在 ask_result_log(uniqueid ,call_date)上建立索引。

[「已注销」]

多谢两位 正在试 是否有效

[Rotel-刘志东]

不要全套子查询,用表的连接查询 select a.phone_number,a.call_date,a.status ,max(case when b.que_id='504' then form_value end) as `Q1` ,max(case when b.que_id='510' then form_value end) as `Q2` ,max(case when b.que_id='511' then form_value end) as `Q3` from visit_log a inner join ask_result_log b on b.uniqueid=a.uniqueid where a.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59' and b.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59' group by a.uniqueid;

[「已注销」]

多谢 多谢，已经改成 连接查询了，性能提升了很多。我再看看优化下！