How do you combine these 3 mySQL queries into one?

Question

The schematic code of what I am trying to do:

INPUT VAR inputOne; (First input of the desired statement)
INPUT VAR inputTwo; (Second input of the desired statement)
INPUT VAR inputThree; (Third input of the desired statement)

-

VAR repResult = getResult("SELECT * FROM `representatives` WHERE `rID` = inputOne LIMIT 1;")
VAR evResult = getResult("SELECT `events`.`eID` FROM `events` WHERE `eventDateTime` = inputTwo LIMIT 1;")

if (repResult != null && evResult != null) {
    execureQuery("INSERT INTO `votes` (`representatives_rID`, `events_eID`, `voteResult`) VALUES(inputOne,evResult.eID,inputThree);");
}

It is quite slow, when I execute them in separated statement, especially because there are ~1.000.000 that needs to be checked and inserted. I was wondering, if there is any alternative, one-query way of doing this.

Putting three queries together into a single query may not have too much effect on the overall speed — What have you tried, Apr 23 '13 at 22:09
Hm, why the `LIMIT 1`, what if more events or representatives match your arguments? I don't see any `ORDER BY`, so just "any random one will do"? — Wrikken, Apr 23 '13 at 22:19

score 3 · Accepted Answer · edited May 23 '17 at 11:43

You can use INSERT-SELECT syntax to accomplish this:

INSERT INTO `votes` (`representatives_rID`, `events_eID`, `voteResult`) 
select inputOne, `events`.`eID`, inputThree FROM `events` WHERE `eventDateTime` = inputTwo LIMIT 1

The above combines all three params into one INSERT-SELECT statement where the results of the select are sent to the insert.

See: Insert into ... values ( SELECT ... FROM ... ) for select-insert statement.

Federico Razzoli · Answer 2 · 2013-04-24T00:39:41.770

Yes, you can put these statements into 1 Stored Procedure (which returns 2 resultsets and performs 1 insert), but no, this probably wouldn't help. Because 3 SQL statements are not a big amount of network traffic, and because Stored Procedures are slow in MySQL.

Is rID a primary key? Does the first query extract big fields you don't really need?

Is there a unique index on eventDateTime? If the table is not InnoDB, the index should explicitly include eID, so it becomes a covering index.

After making those keys, you can drop LIMIT 1.

Are rID and eID datatypes as small as possible?

How do you combine these 3 mySQL queries into one?

2 Answers2