Using joins (mySQL) in a query

Actually in a web application performance has a huge influence on usability. So I often use joins in my queries to provide compact sql queries. But queries with many joins have a bad performance and the user have to wait for a long time. Is there a better opportunity to provide a better performance?


It's true that joins have to be used cautiously but the problem normally lies in wrong indexes or simply more joins than necessary. JOIN and UNION aren't really comparable because they are here for different reasons. While a JOIN expands your table/select 'horizontally' (you get more columns than in your initial table connecting different tables), UNION does that 'vertically' (the columns stay the same but only the rows grow collecting similar data).
Artur Marcin Sz... - Mon, 12/10/2018 - 06:50 :::
I think using joins is more convenient than just focusing on the performance.
Carole Sebah - Mon, 12/10/2018 - 16:02 :::
2 answers

Joins are more expensive than a pure concatenation. In your case maybe UNION or UNION ALL might be a good solution because it parses the first SELECT statement and moves on to the next SELECT statement and adds the results to the end of an output table. The finding of the different connections of the joined tables is a huge cost factor.


This is something that anyone who works with databases will run into sooner or later, thanks for sharing!

Angelos Arampatzis - Mon, 12/10/2018 - 14:17 :::

It seems that you are not using an index. By doing so, performance increases significantly.