.NET (295) administrative (41) Ajax (42) AngularJS (2) ASP.NET (144) bicycle (2) books (180) browser (8) C# (133) cars (1) chess (28) CodePlex (10) Coma (8) database (47) deployment (3) Entity Framework (2) essay (110) flash/shockwave (2) flex (1) food (3) friend (2) game (20) idea (5) IIS (8) javascript (82) LInQ (2) Linux (6) management (4) manga (42) misc (670) mobile (1) movies (90) MsAccess (1) murder (2) music (64) mysql (1) news (99) permanent (1) personal (68) PHP (1) physics (2) picture (307) places (12) politics (13) programming (503) rant (120) religion (3) science (43) Sharepoint (3) software (58) space (1) T4 (2) technology (11) Test Driven Development (4) translation (2) VB (2) video (97) Visual Studio (44) web design (46) Windows API (8) Windows Forms (3) Windows Server (5) WPF/Silverlight (63) XML (11)

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.

No comments: