CODEWITHSUNDEEP
phpmysql
Chris
Chris

Reputation: 374

Is it better do to a union in SQL or separate queries and then use php array_merge?

I have a SQL query that has 4 UNIONS and 4 LEFT JOINS. It is layed out as such:

SELECT ... FROM table1
    LEFT JOIN other_table1
UNION SELECT ... FROM table2
    LEFT JOIN other_table2
UNION SELECT ... other_table3
    LEFT JOIN other_table3
UNION SELECT ... FROM table4
    LEFT JOIN other_table4

Would it be better to run 4 separate queries and then merge the results with php after the fact? Or should I keep them together? Which would provide that fastest execution?

Upvotes: 9

Views: 3637

Answers (4)

Devart
Devart

Reputation: 121932

The UNION clause can be faster, because it will return distinct records at once (duplicated records won't be returned), otherwise you will need to do it in the application. Also, in this case it may help to reduce a traffic.

From the documentation:

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

You can mix UNION ALL and UNION DISTINCT in the same query. Mixed UNION types are treated such that a DISTINCT union overrides any ALL union to its left. A DISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by using UNION with no following DISTINCT or ALL keyword.

Upvotes: 0

Kamal
Kamal

Reputation: 5522

When a query is executed from a programming language, following steps occur

  1. A connection is created to between application and database (or an existing connection is used from pool)
  2. Query is sent to database
  3. Database sends the result back
  4. Connection is released to pool

If you are running N number of queries, above steps happen N number of times, which you can guess will definitely slow down the process. So ideally we should keep number of queries to as minimum as possible.

It will make sense to break a query into multiple parts if single query becomes complex and it gets difficult to maintain and takes a lot of time to execute. In that case too, good way will be to optimize the query itself.

As in your case, query is pretty simple, and as someone has pointed out that union will also help removing duplicate rows, the best way is to go for sql query than php code. Try optimization techniques like creating proper indexes on tables.

Upvotes: 0

sundar
sundar

Reputation: 1760

In this case, it depends on the number of records you are going to get out of the result. Since you are using left join in all unions, I suggest to do different fetch to avoid bottleneck in SQL and merge the results in PHP

Upvotes: 0

Bojangles
Bojangles

Reputation: 101483

The most definitive answer is to test each method, however the UNION is most likely to be faster as only one query is run by MySQL as opposed to 4 for each part of the union.

You also remove the overhead of reading the data into memory in PHP and concatenating it. Instead, you can just do a while() or foreach() or whatever on one result.

Upvotes: 6

Related Questions