Hi Arnold,
On Fri, Jun 13, 2008 at 5:36 AM, Arnold Daniels <info@stripped> wrote:
> I've been using lib_mysqludf_xql (http://www.mysqludf.com) to create XML. To
> get an XML tree I use subqueries which aggregate the data. Unfortunately
> however this doesn't work well since the performance of using subqueries
> isn't good.
>
Yes, this is a known problem, currently all FROM clause subqueries are
materialized into temporary tables which have no indexes. Evgen Potemkin
is now working on FROM subquery optimization (the task is filed as
http://forge.mysql.com/worklog/task.php?id=3485, there is nothing yet
at the url but afaik the work is already in progress. The url can be
used to track progress).
I don't know if WL#3485 includes handling of queries in form
FROM (SELECT x, ... GROUP BY x) tbl WHERE tbl.x=...
but we are aware that this is a practically important case and have an
intent to handle such cases (*).
> When I perform something like:
>
> SELECT xql_agg('Countries' as `xql:root`, 'country' as `xql:child`, `Code`,
> `Name`, `Continent`, `Region`, `cities` as `xql:raw`, `languages` as
> `xql:raw`) FROM `Country`
> LEFT JOIN (SELECT `CountryCode`, xql_agg('cities' AS `xql:root`, `Name` AS
> `City`) AS `cities` FROM `City` GROUP BY `CountryCode`) AS `City` ON
> `Country`.`Code` = `City`.`CountryCode`
> LEFT JOIN (SELECT `CountryCode`, xql_agg('Languages' AS `xql:root`,
> `Language`) AS `languages` FROM `CountryLanguage` WHERE `IsOfficial` = 'T'
> GROUP BY `CountryCode`) AS `CountryLanguage` ON `Country`.`Code` =
> `CountryLanguage`.`CountryCode`
> WHERE `Country`.`Code` IN ('NLD', 'BEL', 'USA', 'XXY');
...
> SELECT 'Countries' as `xql:root`, 'country' as `xql:child`, `Code`, `Name`,
> `Continent`, `Region` FROM `Country` WHERE `Country`.`Code` IN ('NLD',
> 'BEL', 'USA', 'XXY');
> -- Index the values `Country`.`Code` as the resultset is created.
This looks like a hash join. MySQL doesn't support hash joins, but for
this case it can achieve a similar effect with equality propagation -
if we implement (*), equality propagation will work across subquery
bounds, i.e. MySQL would be able infer that
`Country`.`Code` IN ('NLD', 'BEL', 'USA', 'XXY'),
`Country`.`Code` = `CountryLanguage`.`CountryCode`
gives us
`CountryLanguage`.`CountryCode` IN ('NLD', 'BEL', 'USA', 'XXY')
and this predicate can be used within the subquery.
At the moment I can't tell which MySQL version will have FROM subquery
optimizations, but the more requests we get, the higher priority this
receives.
Thanks,
Sergey
--
Sergey Petrunia, Lead Software Engineer
MySQL AB, www.mysql.com
Office: N/A
Blog: http://s.petrunia.net/blog
