How expensive is a join?
It depends! It depends what the join criteria is, what indexes are present, how big the tables are, whether the relations are cached, what hardware is being used, what configuration parameters are set, whether statistics are up-to-date, what other activity is happening on the system, to name a few things.
- But we can still try and get a feel for what a couple simple scenarios look like and see what happens when:
- The number of tables being joined increases
- The number of rows in those tables increases
Indexes are present / not present
It’s interesting that when indexes are being used it almost doesn’t matter how many rows are in the table, as we can see their times are all more or less together.
.. Even with a query that already joins 150 tables w/ 100k rows each, adding another table is only an additional 1.2 ms increase. Cool!
.. Based on the improvement we saw with indexes previously, it’s not too much of a surprise that we get great performance with our million row tables as well. But still, joining 50 tables with 1M rows each, in just 12ms. Wow!