Posted by: robin in MyBlog on Nov 23, 2009
While at MySQL, one of the complaints I used to receive every now and then had to do with MySQL’s performance in the area of UNION’s. I distinctly remember one user group meeting where folks from one government agency told me they couldn’t use UNION at all in their application because the performance was so slow.
Now this isn’t always the case with MySQL, but I’ll admit I have seen bad speed on some UNION queries. And that’s one of the reasons I’m happy that our engineers have just implemented fast UNION support in InfiniDB. Our latest release now supports both UNION and UNION ALL and does so in a pretty efficient manner.
Taking an extreme example – here’s a UNION query and the response times for both InfiniDB and MyISAM (indexed) against a table with 6 million rows with the result set returning about one million rows back:
select l_linenumber, l_orderkey from lineitem where l_orderkey < 1000000
union
select l_linenumber, l_orderkey from lineitem where l_orderkey < 100000;
With MyISAM, the response time for the above UNION query is 49.04 seconds; with InfiniDB it’s 1.43 seconds. But perhaps more noteworthy is the UNION ‘factor’ of each. The individual queries for MyISAM run in 4.68 and 0.47 seconds respectively and 1.23 and 0.16 seconds for InfiniDB. The MySIAM UNION impact factor is 9.52 (calculated by dividing the union query elapsed time by the sum of the two individual queries’ response times) whereas the InfiniDB UNION factor is 1.03. Performance degrades for MyISAM greater than 9x with the UNION but is barely noticeable with InfiniDB.
Now, MyISAM improves quite a bit when you use UNION ALL instead of UNION, which negates the elimination of duplicate rows. MyISAM’s UNION ALL query runs in a little over 6 seconds, which is much better, and InfiniDB’s is 1.24 seconds. The MyISAM factor there is 1.16, and InfiniDB’s is .89.
In any event, I’m glad InfiniDB now has efficient UNION and UNION ALL support and kudos to the Calpont engineers who made it happen.
Please download the latest version of InfiniDB with UNION/ALL support and let us know what you think.