优化SQL语句,该怎么处理
优化SQL语句
数据量： 几十万行
------解决方案--------------------
SELECT co,MAX(c1) AS c1,c4,c9 FROM table_2 GROUP BY co
这个全表分组求最大耗时吗
------解决方案--------------------
贴一下你的 show index from a; show index from b; ...
------解决方案--------------------
贴出执行计划show index from a; show index from b;
- SQL code
SELECT a.co
,a.c4
,b.c
,b.c9
,c.c9
,b.c4/b.c9*10000
,c.c4/c.c9*10000
FROM table_1 a
RIGHT JOIN
(SELECT co,MAX(c1) AS c1,c4,c9 FROM table_2 GROUP BY co )b
ON a.co=b.co
LEFT JOIN table_2 c
ON b.co=c.co AND b.c1>c.c1 AND NOT EXISTS(SELECT 1 FROM table_2 WHERE co=b.co AND c1<b.c1 AND c1>c.c1)
WHERE YEAR(b.c1)=YEAR(20120331000000) AND QUARTER(b.c1)=QUARTER(20120331000000)
数据量： 几十万行
------解决方案--------------------
SELECT co,MAX(c1) AS c1,c4,c9 FROM table_2 GROUP BY co
这个全表分组求最大耗时吗
------解决方案--------------------
贴一下你的 show index from a; show index from b; ...
------解决方案--------------------
贴出执行计划show index from a; show index from b;