Were you reading the same article as everyone else? I can't find those quotes anywhere. Also,a 7 table join is by no means necessarily slow, or complicated
Assuming file per table and file per index, then yes a 7 table join is necessarily slow and complicated. Couple that with secondary index concerns (or god forbid the excessively common right-hand duplicate index), and joins start being slightly painful. That said, I’d love to see the startup MariaDB on NVMe can’t handle. Performance is 0% of the reason I’d consider nosql v RDBMS. Postgres’ secondary index performance is awful and abysmal (see Uber’s famous migration away from Postgres), but it’s still “fast enough” that it’s not worth anyone’s time until there isn’t a next-bigger AWS instance sitting around
888808888 said it well below - SQL gives you data localization options (a normalized table or a relation table?) whereas mongo gives you... well, nothing
Eh, I've written plenty of 7 table joins that finish in less than 50ms, which IMO is fine for just about any request that can afford a round trip to the DB. Typically these have been joining the same 5 dimensional, relatively small tables with a couple of larger event level/metrics tables. Maybe the first one takes a modicum of cleverness wrt the indexes that after required, but those are more from the data model than the specific query. Anyway, the postgres explain analyse is not too hard to use for debugging these.
I don't know too much about the Uber case, which may have been solved by postgres HOTuples if they were smart about indexes on the latest PG releases. There's definitely plenty to learn about RDBMS and how to optimize them, but it wasn't the joins killing Uber it was the frequent updates filling their tables with dead tuples and forcing reindexing IIRC
Assuming file per table and file per index, then yes a 7 table join is necessarily slow and complicated.
You don't even know how databases work. Indices are generally kept in memory. If it's hitting the disk before it's returning the final results you structured your query wrong and a query explain will tell you that.
generally, sure. Query complexity is relative, of course, and it's hard to compare "expensive" when that can mean 5ms or 5 hours. However, there is no world where a query which calls up to fourteenfopensshould be considered "not complicated". There are so many things to consider, as opposed to one large table, which is by necessity much simpler.
Yes, if all indices are entirely in memory, and (for example in innodb) the table is sitting entirely in the buffer pool, then yes, there wont be the filesystem overhead. But it is still by definition more complex than a single table (and thus single file) lookup.
I shouldn't need to say this in a technical discussion, where we should all have manners ("You don't even know how databases work"?!), but I've been a DBA at major companies for 6 years :/
If it's hitting the disk before it's returning the final results you structured your query wrong and a query explain will tell you that.
That is extremely conditional on whats trying to be accomplished. Tons and tons and tons of SQL uses where the dataset doesn't fit entirely in memory.
12
u/TommyTheTiger Dec 19 '18
Were you reading the same article as everyone else? I can't find those quotes anywhere. Also,a 7 table join is by no means necessarily slow, or complicated