Re: UDF XML functions

[Arnold Daniels]

Hi,

First of all the first version of the UDF library to create XML is 
finished. It can be found at http://libmysqlbnxml.sourceforge.net/. I 
could appreciate it if people could have a look and give any remarks 
about the code, especially security flaws.

Next, I'm not able to get a tree result within a decent time, because 
I'm unable to get MySQL to do what I want it to do. The problem breaks 
down to this:

SELECT id, bedrijfsnaam, achternamen, adressen FROM relatie
 LEFT JOIN (SELECT relatie_id, GROUP_CONCAT(achternaam) AS achternamen 
FROM contactpersoon GROUP BY relatie_id) AS contactpersoon ON relatie.id 
= contactpersoon.relatie_id
 LEFT JOIN (SELECT relatie_id, GROUP_CONCAT(adres) AS adressen FROM 
relatie_adres GROUP BY relatie_id) AS relatie_adres ON relatie.id = 
relatie_adres.relatie_id
LIMIT 50

Executing a query like this is extremely slow. All subqueries are 
executed fully and the joins are done by table scans. Does anyone have 
any suggestion in how to speed up getting aggregated data from the 
database without have to split up query and doing all the work at the 
client.

Thanks,
Arnold



Brian Aker schreef:
> i!
>
> On Aug 14, 2006, at 7:14 PM, <mysql@stripped> <mysql@stripped> 
> wrote:
>
>> I've looked on the internet, but the best thing I found is MyXML, which
>> is simply too limited (and looks not supported). Is anyone currently
>
> MyXML is a pretty trivial thing, and while I know of a few places 
> that  use it, it does not have mass appeal :)
>
> It is supported as far as bugs go, but no one has send me a bug on it 
> in years.
>
> Cheers,
>     -Brian
>
>> already working on this or is MySQL AB itself working on XML output
>> functions.
>>
>> I've also got a direct question. What is exactly the function of
>> group_concat_max_len? I got the impression that memory the size of
>> group_concat_max_len is allocated for each group_concat call.
>> If so, I hope to prevent doing the same using xmlwriter. I'm not sure,
>> but at the first glance it looks like xmlwriter allocates memory
>> dynamically.
>>
>> I'm not sure about performance; using lots of group_concat's can really
>> slow a query down. But we'll just have to see after I've written some
>> code.
>>
>> If anyone has any tips, remarks, etc, please let me know.
>>
>>
>> ------
>>
>> Proposed syntax:
>> SELECT xmlelement("root", xmlgroup(trunk.xmlnode)) FROM
>>   (SELECT xmlelement("trunk", xmlgroup(leaf.xmlnode),
>> xmlattributes("id", trunk.id, "description", trunk.description)) AS
>> xmlnode FROM tree_trunk LEFT JOIN
>>     (SELECT tree_trunk_id, xmlelement("leaf", NULL, xmlattributes("id",
>> tree_leaf.id, "description", tree_leaf.description)) AS xmlnode FROM
>> tree_leaf) AS leaf ON tree_trunk.id = leaf.trunk_id
>>   GROUP BY tree_trunk.id) AS trunk
>>
>> ------
>>
>> Simulation:
>> SELECT CONCAT_WS('', "<root>", GROUP_CONCAT(trunk.xmlnode SEPARATOR ''),
>> "</root>") FROM
>>   (SELECT CONCAT_WS('', "<trunk id=", QUOTE(tree_trunk.id), "
>> description=", QUOTE(tree_trunk.description), ">",
>> GROUP_CONCAT(leaf.xmlnode SEPARATOR ''), "</trunk>") AS xmlnode FROM
>> tree_trunk LEFT JOIN
>>     (SELECT tree_leaf.trunk_id, CONCAT_WS('', "<leaf id=",
>> QUOTE(tree_leaf.id), " description=", QUOTE(tree_leaf.description), "
>> />") AS xmlnode FROM tree_leaf) AS leaf ON tree_trunk.id = leaf.trunk_id
>>   GROUP BY tree_trunk.id) AS trunk
>>
>> Output (query does not format xml):
>> <root>
>>   <trunk id='1' description='ab'>
>>     <leaf id='1' description='ab_aa' />
>>   </trunk>
>>   <trunk id='2' description='bc'>
>>     <leaf id='2' description='bc_aa' />
>>     <leaf id='3' description='bc_ab' />
>>   </trunk>
>>   <trunk id='3' description='cd'></trunk>
>>   <trunk id='4' description='de'></trunk>
>>   <trunk id='5' description='ef'>
>>     <leaf id='4' description='ef_aa' />
>>   </trunk>
>>   <trunk id='6' description='fg'></trunk>
>>   <trunk id='7' description='gh'>
>>     <leaf id='5' description='gh_aa' />
>>   </trunk>
>>   <trunk id='8' description='hi'>
>>     <leaf id='6' description='hi_aa' />
>>   </trunk>
>> </root>
>>
>> ------
>>
>> Met vriendelijke groet,
>>
>> Arnold Daniels
>> Bean IT
>>
>
> -- 
> _______________________________________________________
> Brian "Krow" Aker, brian at tangent.org
> Seattle, Washington
> http://krow.net/
> http://tangent.org/
> _______________________________________________________
> You can't grep a dead tree.
>
>
>