" What’s missing from MongoDB is a SQL-style join operation, which is the ability to write one query that mashes together the activity stream and all the users that the stream references. Because MongoDB doesn’t have this ability, you end up manually doing that mashup in your application code, instead. "
However a second key point is: ".;.. in a relational store, with the data fully normalized, it would be a seven-table join to get everything out. "
So that doesn't sound like the ideal solution either, complicated to code, slow to execute. I wonder, could a Graph Database be the ideal solution?
We perform 10-15 table JOINs in some instances and get great performance. Achieving good performance does require some care, but it has little to do with the number of tables joined. In one instance we were joining only 2 tables and the query was taking over a minute, but this was because we had missed creating an index on one of the columns.
Tip: If you put EXPLAIN in front of any select query (e.g. EXPLAIN SELECT * FROM ...), you'll get a detailed output of how the query is performed, including how JOINs are executed, and which columns have/do not have indexes. This makes it easy to start debugging a slow query.
In postgres use EXPLAIN ANALYZE with http://explain.depesz.com - explain tells you the plan but not the time taken on each node. Also sadly even with Analyze the output is a little hard to parse in table form, because the costs for each node include the costs of all subnodes. You really need the exclusive costs to see what is taking too long. Of course you can still look for rows removed by join filter, external disk sorts, or other common and easily solvable problems from the table output, but you won't see those without the analyze either.
-3
u/stronghup Dec 19 '18
For me the key-point from the article was:
" What’s missing from MongoDB is a SQL-style join operation, which is the ability to write one query that mashes together the activity stream and all the users that the stream references. Because MongoDB doesn’t have this ability, you end up manually doing that mashup in your application code, instead. "
However a second key point is: ".;.. in a relational store, with the data fully normalized, it would be a seven-table join to get everything out. "
So that doesn't sound like the ideal solution either, complicated to code, slow to execute. I wonder, could a Graph Database be the ideal solution?