JOIN, UNION or two SELECTs?

apzer0

The title kinda says it all, which is faster?
(Using mysql 4.0.22)

bastien

it depends on the data load on the system and how many joins...I have had to remove joins and move to individual selects and arrays to manage the data for reports. Reduces a 5 minute procedure to about 5 seconds.

apzer0

Just 2 SELECT querys, I would think UNION is the best but am not sure.

bastien

Are the dataset field types the same from both tables and in the same order? Otherwise the union won't work.

mourisj

JOIN is used to select data from different tables related to each other by one or more common identifiers.

UNION is used to merge the data from two or more different queries;each of these queries can affect different tables, different criteria and different relationships. But the number of fields and the datatypes have to be identical.

Several SELECTs might give the same result as the union, but you need to process the result sets afterwards (e.g. with PHP).

Performance can depend of multiple factors (number of tables joined, other selection criteria, table size / result set size, sort criteria, ...)

I have never systematically measured the performance of this kind of thing.

But I had once a very complex UNION statement (on ORACLE D😎 : 3 different select statements, each with different conditions, sub-selects, etc. In the beginning, performance was very bad (about 20 sec.) . Then I examined each single statement; one of them was not very fast, but the overall performance was MUCH lower than of the single statement. After a thorough analysis, I added an additional WHERE clause, which lead to the same results, but increased the performance of the complete statement (UNION of 3 selects) to less than 1 sec. !!

JJ Mouris