StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODatabase Structure for hierarchical data with horizontal slices
text
Body
copied!<p>We're currently looking at trying to improve performance of queries for our site, the core hierarchical data-structure has 5 levels, each type has about 20 fields.</p> <pre><code>level1: rarely added, updated infrequently, ~ 100 children level2: rarely added, updated fairly infrequently, ~ 200 children level3: added often, updated fairly often, ~ 1-50 children (average ~10) level4: added often, updated quite often, ~1-50 children (average <10) level5: added often, updated often (a single item might update once a second) </code></pre> <p>We have a single data pipeline which performs all of these updates and inserts (ie. we have full control over data going in).</p> <p>The queries we need to do on this are:</p> <pre><code>fetch single items from a level + parents fetch a slice of items across a level (either by PK, or sometimes filtering criteria) fetch multiple items from level3 and parts of their children (usually by complex criteria) fetch level3 and all children </code></pre> <p>We read from this datasource a lot, as-in hundreds of times a second. All of the queries we need to perform are known and optimised as well as they can be to the current data structure.</p> <p>We're currently using MySQL queries behind memcached for this, and just doing additional queries to get children/parents, I'm thinking that some sort of Tree-based or Document based database might be more suitable.</p> <p>My question is: what's the best way to model this data for efficient read performance?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload