CODEWITHSUNDEEP
mysql
Jason Swett
Jason Swett

Reputation: 45074

Queries run fast separately, slowly under UNION

I had a query that was taking several minutes to run. I thought I might be able to get it to run faster as four separate queries, then a UNION. I was at least right about the four separate queries part. They run, from slowest to fastest, ~7 seconds, ~1 second, < 1 second and < 1 second.

So I would think that the UNION of all these would run, at the slowest, about 7 + 1 + 1 + 1 = 10 seconds. However, when I run this UNION query, it takes over 100 seconds.

(select id from view_macatawa_prospect_match_first_and_last_name)
union
(select id from view_macatawa_prospect_match_full_name)
union
(select id from view_macatawa_prospect_match_just_address)
union 
(select id from view_macatawa_prospect_match_name_and_address)

Why would UNION make these all slower? It seems to me that all MySQL has to do is execute the four queries then concatenate the results.

Edit: I guess I didn't ask what I'm really after: a way to make this query run quickly.

Upvotes: 2

Views: 1293

Answers (2)

d-live
d-live

Reputation: 8036

Use 'UNION ALL' instead

UNION tries apply 'DISTINCT' implicitly, meaning your results will not have any duplicates hence the slowdown. If you dont want the implicit DISTINCT you can use UNION ALL.

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

The problem is that union removes duplicates. So it's not just about running the queries.

Try using union all instead of union.

That way, duplicates won't be removed:

(select id from view_macatawa_prospect_match_first_and_last_name)
union all
(select id from view_macatawa_prospect_match_full_name)
union all
(select id from view_macatawa_prospect_match_just_address)
union all
(select id from view_macatawa_prospect_match_name_and_address)

With union all you should get times closer to what you've got when you run the queries separately.

Upvotes: 2

Related Questions