MySQL加入速度很慢

Question

我们的一个 MySQL 语句有问题。

基本上，以下语句需要 5 秒才能运行。我们已经确诊这取决于两个 select 语句的连接。当选择语句是单独运行它们只需要 0.2 秒，但是当与 JOIN 结合使用时需要 5 秒。

有什么你能看出我们做错了什么，或者你能看到更好的地方吗？怎么办？

索引已添加到连接中包含的所有列，但不影响速度

SELECT temp_4.primaryid, temp_1.`subjectID` , temp_4.`testOccasionID` 
,`studyNumbers` ,`testDate` 

FROM (
   SELECT * FROM (
     SELECT primarys.primaryid , q_1 AS `subjectID` , q_2 AS `studyNumbers` FROM 
primarys LEFT OUTER JOIN questions_1_100 ON primarys.primaryid = 
questions_1_100.primaryid WHERE 0 = 0 AND q_1 IS NOT NULL GROUP BY primaryid) AS 
maintable_1 
GROUP BY `subjectID` ) AS temp_1 

JOIN 

(SELECT * FROM 
(SELECT primarys.primaryid , q_1 AS `subjectID` , q_4 AS `testOccasionID` , 
DATE_FORMAT(q_5, '%m/%d/%Y') AS `testDate` FROM primarys LEFT OUTER JOIN 
questions_1_100 ON primarys.primaryid = questions_1_100.primaryid WHERE 0 = 0 AND 
q_1 IS NOT NULL AND q_4 IS NOT NULL GROUP BY primaryid) AS maintable_4 
GROUP BY `subjectID` ,`testOccasionID` ) AS temp_4 

ON temp_1.`subjectID` = temp_4.`subjectID` 

表定义:

CREATE TABLE primarys 
( primaryid BIGINT(20) NOT NULL AUTO_INCREMENT,
  dateinserted DATETIME,
  datemodified DATETIME,
  useridinserted BIGINT(20),
  useridmodified BIGINT(20),
  locked VARCHAR(1) NOT NULL DEFAULT 0, 
  primaryquestionlinks TEXT, 
  PRIMARY KEY (primaryid), 
  FOREIGN KEY (useridinserted) REFERENCES users (userid) ON UPDATE CASCADE ON DELETE SET NULL, 
  FOREIGN KEY (useridmodified) REFERENCES users (userid) ON UPDATE CASCADE ON DELETE SET NULL ) ENGINE=InnoDB;

CREATE TABLE questions_1_100
( primaryid BIGINT(20) NOT NULL,
  q_1 BIGINT(20),
  q_2 VARCHAR(50),
  q_3 BIGINT(20),
  q_4 BIGINT(20),
  q_5 DATE,
  PRIMARY KEY (primaryid),
  FOREIGN KEY (primaryid) REFERENCES primarys (primaryid) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB;

单个行军主体的数据如下，顺序为primaryid,q_1,q_2,q_4,q_5:

1    1    01001    NULL    NULL
7286 1    NULL     1       1997-12-18
7287 1    NULL     2       1998-02-25

所需的输出是:

7286 1    01001    1      1997-12-18
7287 1    01001    2      1998-02-25

非常感谢

更进一步...如果 q_1 和 q_4 在两个单独的表中会怎样。比如下面的表结构。我能想到的唯一方法是添加左外连接和几个子查询？

CREATE TABLE primarys 
( primaryid BIGINT(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (primaryid));

CREATE TABLE questions_1_100
( primaryid BIGINT(20) NOT NULL,
q_1 BIGINT(20),
q_2 VARCHAR(50),
PRIMARY KEY (primaryid));

CREATE TABLE questions_101_200
( primaryid BIGINT(20) NOT NULL,
q_4 BIGINT(20),
q_5 DATE,
PRIMARY KEY (primaryid));

INSERT INTO primarys values (1);
INSERT INTO primarys values (7286);
INSERT INTO primarys values (7287);

INSERT INTO questions_1_100 VALUES (1,'1','01001');
INSERT INTO questions_1_100 VALUES (7286,'1','');
INSERT INTO questions_1_100 VALUES (7287,'1','');

INSERT INTO questions_101_200 VALUES (7286,'1','1997-12-18');
INSERT INTO questions_101_200 VALUES (7287,'2','1998-02-25');

Answer 1

试试这个查询

您正在创建第二个没有用的内部查询。最好删除那个。

创建这些索引表 questions_1_100 compund index (primaryid, q_1, q_4)

表 primaries 索引(primaryid)

希望这有帮助..

SELECT 
  temp_4.primaryid, 
  temp_1.`subjectID` , 
  temp_4.`testOccasionID` ,
  `studyNumbers` ,
  `testDate` 
FROM (
  SELECT 
    primarys.primaryid , 
    q_1 AS `subjectID` , 
    q_2 AS `studyNumbers` 
  FROM 
    primarys 
  LEFT OUTER JOIN 
    questions_1_100 
  ON 
    primarys.primaryid = questions_1_100.primaryid 
  WHERE 
    q_1 IS NOT NULL 
  GROUP BY 
    primaryid, 
    subjectID) AS temp_1 
JOIN 
  (SELECT  
     primarys.primaryid , 
     q_1 AS `subjectID` , 
     q_4 AS `testOccasionID` , 
     DATE_FORMAT(q_5, '%m/%d/%Y') AS `testDate` 
  FROM 
     primarys 
  LEFT OUTER JOIN 
     questions_1_100 
  ON 
     primarys.primaryid = questions_1_100.primaryid 
  WHERE 
     q_1 IS NOT NULL AND 
     q_4 IS NOT NULL 
  GROUP BY 
     primaryid,
     subjectID,
     testOccasionID) AS temp_4 
ON 
  temp_1.`subjectID` = temp_4.`subjectID`