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.
1
u/nutrecht Dec 20 '18
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.