我有一个mySQL查询,它正在运行,但是涉及到160万次执行时,由于NOT EXIST(new query)的不断重新执行,它不能满足我所需的性能。

INSERT INTO `votes` (`representatives_rID`, `events_eID`, `voteResult`)
SELECT `representatives`.`rID`,`events`.`eID`,?
FROM `representatives`, `events`
WHERE `events`.`eventDateTime` = ?
AND `representatives`.`rID` = ?
AND NOT EXISTS (SELECT * FROM `votes`
                WHERE `votes`.`representatives_rID` = `representatives`.`rID`
                AND `votes`.`events_eID` = `events`.`eID`);


在示意图中:

if (input one is in `representatives` AND input two is in `events` AND there is no row in `votes` that contains ( `votes`.`representatives_rID` = `representatives`.`rid` and `votes`.`events_eID` = `events`.`eID)) {
    insert a row into votes containing `eid` from `events` and `rid` from `representatives` and input three;
}


有没有什么办法可以使速度更快(可能是通过join)?

最佳答案

INSERT IGNORE INTO votes
(representatives_rID
,events_eID
,voteResult
)
SELECT r.rID
     , e.eID
     , ?
  FROM representatives r
 CROSS
  JOIN events e
    ON e.eventDateTime = ?
   AND r.rID = ?;


09-26 03:03