Have Geek Brain

Is hierarchical data in MySQL as fast as XML to retrieve?

I have a list of all countries -> states -> cities (-> undercuts / villages, etc.) in an XML file and for example to extract the state of all cities where it is really fast with XML (using the xml parser).

I wonder if I put all this information in mysql, restores the state of all cities as quickly as with XML? the reason why XML is designed to store hierarchical data while relational databases like mysql are not.

the list contains as much as 500,000 objects. so I'm wondering if it could be as fast as XML using any of:

The child list model

Nested set model

And which one should I use? The reason (in theory) could be an unlimited level under the state (I heard that adjacency is not suitable for an unlimited level of children). And what's faster for this huge dataset?

Thanks!

+2


a source to share


1 answer


In this article, Quassnoi creates a table with 2,441,405 rows in a hierarchical structure and tests the performance of highly optimized queries for nested sets and adjacency lists. It runs many different tests, such as picking ancestors or descendants and multiplying the results (read the article for more details on what was tested):

                                      Nested Sets Adjacency Lists
All descendants 300ms 7000ms
All ancestors 15ms 600ms
All descendants up to a certain level 5000ms 600ms

His conclusion is that, for MySQL, nested sets are faster to query, but have the disadvantage that it is much slower to update. If you have rare updates, use nested sets. Otherwise adjacency lists are preferred.



You might also want to consider using another database that supports recursive CTEs.

I would imagine that an XML file of this size would take quite a long time to parse, but if you can cache the parsed structure in memory rather than read it from disk every time, then queries against it will be very fast.

Please note that the main disadvantage of using MySQL to store hierarchical data is that it requires some very complex queries. While you can just copy the code from the article I linked to, if you ever need to change it a bit, then you need to understand how it works. If you prefer to keep things simple, then XML definitely has an advantage as it is intended for this type of data, so you should easily create the queries you want.

+2


a source






More articles:


All Articles