Does multiple table join slows down mysql

Question

My simple question is : Does multiple table join slows down mysql performance?

I have a data set where I need to do about 6 tables JOIN, on properly indexed columns.

I read the threads like

Join slows down sql MySQL adding join slows down whole query MySQL multiple table join query performance issue

But the question remains still as it is.

Can someone who experienced this thing reply?

Answer 1

MySQL, by default, uses the Block Nested-Loop join algorithm for joins.

SELECT t1.*, t2.col1
FROM table1 t1
LEFT JOIN table2 t2
  ON t2.id = t1.id

In effect, yields the same performance as a subquery like the following:

SELECT t1.*, (SELECT col1 FROM table2 t2 WHERE t2.id = t1.id)
FROM table1 t1

Indexes are obviously important to satisfy the WHERE clause in the subquery, and are used in the same fashion for join operations.

The performance of a join, assuming proper indexes, amounts to the number of lookups that MySQL must perform. The more lookups, the longer it takes.

Hence, the more rows involved, the slower the join. Joins with small result sets (few rows) are fast and considered normal usage. Keep your result sets small and use proper indexes, and you'll be fine. Don't avoid the join.

Of course, sorting results from multiple tables can be a bit more complicated for MySQL, and any time you join text or blob columns MySQL requires a temporary table, and there are numerous other details.