Combine two MySQL queries with UNION or programmatically
I have two MySQL queries that insert data into a table. Both have the following format:
CREATE TABLE IF NOT EXISTS `data` (
`id` BIGINT NOT NULL AUTO_INCREMENT UNIQUE,
PRIMARY KEY (`id`)
)
SELECT `field1`, `field2`
WHERE `active` = 1
The only differences between the two queries are the definition of field1
and field2
and some minor differences in the conditional conditions. Both users are running up to 12 KB or more records.
Now, which would be more efficient:
and. Run both queries separately:
if (mysql_query($query1)) {
return mysql_query($query2);
}
return false;
Q. OR combine the two queries with UNION and execute once:
$query = 'SELECT `field1`, `field2` WHERE `active` = 1
UNION
SELECT DO_ONE(`field1`), DO_TWO(`field2`) WHERE `active` = 1
ORDER BY `field1`';
return mysql_query('CREATE TABLE IF NOT EXISTS `data` (
`id` BIGINT NOT NULL AUTO_INCREMENT UNIQUE,
PRIMARY KEY (`id`)
) ' . $query)
Data from one request is useless without data from the other, so both should succeed. DO_ONE
and DO_TWO
are MySQL user-defined functions that modify field data according to some specification.
a source to share
Aaronmccall's answer is probably the best overall - the UNION approach does it all in a single SQL call. In general this will be the most "efficient", but there may be side issues that can come into play and affect the measure of "efficiency" for your particular application.
In particular, if the UNION requires a temporary table to collect intermediate results, and you are working with very large datasets, then performing two separate direct SELECTs on the new table may be more efficient in your particular case. This will depend on internal actions, optimizations performed, etc. Inside the database engine (which can change depending on the version of the database engine you are using).
Ultimately, the only way to answer your question on a specific question like this is to do the timings for your specific application and environment.
You might also think that the difference between the time it takes for two separate requests and an "all in one" request might be negligible in the grand scheme of things ... you are probably talking about a few millisecond (or even microsecond?) Difference if your mysql database is not on a separate server with huge latency issues. If you make thousands of these calls in one shot, the difference can be significant, but if you only make one or two of those calls and your application is spending 99.99% of its time doing other things, then the difference between the two probably isn't even will be noticed.
--- Lawrence
a source to share
Your options do different things. The first returns the results of the second query if the first query runs correctly (that BTW doesn't depend on the results it returns, it can return an empty rowset) The second returns the results of the first query and the second query together. The first option seems pretty gimmicky to me, maybe what you want to achieve is what you did with UNION (if I haven't missed you).
EDIT: After reading your comment, I think you are after something like this:
SELECT true where (EXISTS (SELECT field1, field2 ...) AND EXISTS (SELECT Field1, field2 ...)).
This way, you only have one DB query that scales better, takes up less resources from the connection pool, and doesn't double the latency impact if you have a DB engine on another server, but still abort the query if the first condition fails. , which is the performance improvement you are looking for with nested individual queries.
As an optimization, try to fulfill the condition first, which will run faster if they don't match. I guess if one of them requires these field calculations to be slower.
a source to share