在MySQL查询上运行解释会产生语法错误。

I have a basic query that looks like the following that's running slow, so I'm trying to figure out where there's missing indexes or other optimizations I can make:

我有一个基本的查询，它看起来像下面的运行缓慢，所以我想找出哪里有缺失的索引或者其他的优化:

drop table weights;
CREATE TABLE weights engine=myisam
SELECT 'K' AS STAT,
           z.WEIGHT,
           COUNT(*) AS SAMPLE,
           round(AVG(z.FINAL_2Y),15) AS 2Y,
           round(AVG(z.FINAL_3Y),15) AS 3Y,
           round(AVG(z.FINAL_4Y),15) AS 4Y,
           round(AVG(z.FINAL_5Y),15) AS 5Y,
           round(AVG(z.FINAL_6Y),15) AS 6Y
    FROM
      ( SELECT /* insert big query here */ ) z
    GROUP BY WEIGHT;

This query is running awfully slow so I'm trying to make one simple change with the following:

这个查询的运行速度非常慢，所以我尝试用以下方法进行一个简单的更改:

drop table sp_weights_holding_table
CREATE TABLE sp_weights_holding_table engine=myisam
EXPLAIN SELECT 'K' AS STAT,
               z.WEIGHT,
               COUNT(*) AS SAMPLE,
               round(AVG(z.FINAL_2Y),15) AS 2Y,
               round(AVG(z.FINAL_3Y),15) AS 3Y,
               round(AVG(z.FINAL_4Y),15) AS 4Y,
               round(AVG(z.FINAL_5Y),15) AS 5Y,
               round(AVG(z.FINAL_6Y),15) AS 6Y
        FROM
          ( SELECT /* insert big query here */ ) z
        GROUP BY WEIGHT;

As soon as I pop the EXPLAIN in, I get a syntax error. I'm using MySQL 5.6.13 on Amazon RDS. The error looks like the following:

一旦我弹出解释，就会出现语法错误。我在Amazon RDS上使用了MySQL 5.6.13。这个错误看起来如下:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXPLAIN SELECT 'K' AS STAT,
       z.WEIGHT,
       COUNT(*) AS SAMPLE,
       r' at line 2

2 个解决方案

#1

You must semicolon the end of the sql command.

您必须在sql命令的末尾分号。

Change to

改变

drop table sp_weights_holding_table;
CREATE TABLE sp_weights_holding_table engine=myisam;
EXPLAIN SELECT 'K' AS STAT,
....

#2

Use the EXPLAIN with the SELECT query alone without the first two lines for dropping/creating the table:

仅使用SELECT查询来使用EXPLAIN，而不使用前两行来删除/创建表:

EXPLAIN SELECT 'K' AS STAT,
           z.WEIGHT,
           COUNT(*) AS SAMPLE,
           round(AVG(z.FINAL_2Y),15) AS 2Y,
           round(AVG(z.FINAL_3Y),15) AS 3Y,
           round(AVG(z.FINAL_4Y),15) AS 4Y,
           round(AVG(z.FINAL_5Y),15) AS 5Y,
           round(AVG(z.FINAL_6Y),15) AS 6Y
    FROM
      ( SELECT /* insert big query here */ ) z
    GROUP BY WEIGHT;

#1