Saturday, February 13, 2010

MySql UNION associability

In simple words, it seems there is none. After doing a query like SELECT stuff FROM (SELECT * FROM Table1 UNION ALL SELECT * FROM Table2) x WHERE condition, where Table1 and Table2 are identical in signatures, I noticed that it took 30 seconds on a combined 1 million rows. So I thought I would try to move the condition on each of the UNIONed tables: SELECT stuff FROM (SELECT * FROM Table1 WHERE condition UNION ALL SELECT * FROM Table2 WHERE condition) and it took 2 seconds.

So it seems as the server performed the union on all the rows, perhaps moving them in a temporary table, then filtered on the condition.

Coming from the world of Microsoft Sql Server which carefully creates a query execution tree and routinely solves queries similar to there two in an identical and optimised fashion, I was a bit surprised. Then again, being a Linux MySQL, maybe it was just not compiled right or didn't have an obscure option set up or something like that. Anyway, I was dissapointed.