Natively Handling Relationships without Hard Limits

Wanted to share a quick side note while fresh on my mind, and another reason why we use Dgraph :smile:

In our SaaS startup we allow users to build filters very dynamically. In our previous LAMP stack, the MySQL queries were dynamically built and filtered. If a user wanted to filter on joined data, we would join the data in the query and add the where conditions all dynamically. This allowed for a very granular filter configuration by the end user based upon almost any piece of connected data. The number one filtering concept users adopt is filtering by tags. Users can select tags that the data should should have ALL of, have ANY of, and have NONE of. The trick with these is that in our database architecture to filter with these tag refinements we had to join pivot tables of the tag relationships and then add condition clauses looking for filled or empty columns. This has been working until today when a user tried to filter with 72 tags. We ran into a new problem, MySQL cannot process a query if it has more than 61 table joins. Which is a hard limit in the database and cannot be configured around.

This reminded me of when we were developing our own GraphQL layer on top of a RDS database (MySQL) we were getting around the N+1 problem by dynamically building some pretty complex queries dynamically heavy on joins and conditions instead of making multiple round trips to the database for every data type joined. In hindsight, this would also have failed eventually when we tried to join 61 or more tables together.

While looking for a quick work around to this problem for our legacy users while we migrate to our new system, I learned that this is a hard limit posed by the database itself and not something that I can configure around. I also read a relational database expert advise to never join more than 7 tables together in a single query or else it would degrade the performance of the query. (Whoops, I definitely was not following that in our legacy LAMP app.)

Limiting a relational database from making relationships feels very awkward, and maybe it should change it’s name from being a relational database if it does not handle over 61 relationships in a single query. This would be like a graph database saying it was impossible to query a graph with 61 edges. This just shows once again why graph databases are the future. Relational databases have their use cases, but not for creating a application full of related data that needs to be easily filtered.

Going through this today helped confirm once again that we are on the right track using a graph database since I could ensure users that this would not be a problem once we migrate them to the new application.


sixty… one? Thanks MySQL.

Interesting read, thanks for sharing.

A bit of nit to pick: A “relation” in a relational database does not refer to a relationship. A “relation” in a relational database is the academic name for what we programmers call a table. A relational database is basically a database where data is stored on tables.

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.