CODEWITHSUNDEEP
mysqlsqllogic
skiwi
skiwi

Reputation: 69279

MySQL (InnoDB) - How many joins makes sense?

I have deloped some program that auto-generates some code used to automatically generate queries in a structured way in Java.

The latest option that I have added is to get a result of one table, while actually specifying constraints for some other table. The only precondition is that those tables have foreign keys to eachother.

I'll only deal with the actual SQL queries here.

This is a valid SQL query which is often used:

SELECT businessPartners.businessPartnerId, businessPartners.name
FROM businessPartners
JOIN BP_emails ON businessPartners.businessPartnerId = BP_emails.businessPartnerId
JOIN emails ON BP_emails.emailId = emails.emailId
WHERE emails.email = "[email protected]"

It selects business partners based on their e-mail adres. businessPartners.businessPartnerId and emails.emailId are both primary keys and BP_emails has the foreign keys in it.

A similar structure is being used for invoices and links between invoices and email.

So I have also found (and verified) that it is possible to do this query:

SELECT businessPartners.businessPartnerId, businessPartners.name
FROM businessPartners
JOIN BP_emails ON businessPartners.businessPartnerId = BP_emails.businessPartnerId
JOIN emails ON BP_emails.emailId = emails.emailId
JOIN INV_emails ON emails.emailId = INV_emails.emailId
JOIN invoices ON INV_emails.invoiceId = invoices.invoiceId
WHERE invoices.invoiceId >=1
AND invoices.invoiceId <=1

First of all I have a hard time figuring out what it exactly means: I think it means something like: Give me all business partners which have invoices.invoiceId = 1 and where the email related to the invoice is the same as the email related to the business partner... So not much sense I think.

So the question is: Up till where do multiple joins actually make sense? I have already had the need for two joins in my first example, are there legitimate examples of needing 3+ joins?

Any help would be appreciated with this.

Upvotes: 2

Views: 87

Answers (3)

duffymo
duffymo

Reputation: 308813

The rule of thumb I've heard is that more than seven tables in a JOIN is too many.

The key thing here is not the number of JOINs, but the proper ordering of the WHERE clauses. SQL is set-based, so if you execute the WHERE clause that excludes the maximum number of rows first you'll save work for subsequent filters.

Indexing will affect performance, too. Make sure you have indexes on all columns in WHERE clauses.

It goes without saying that every table must have a primary key, and that's what you should JOIN on.

Sorry, this is stupid: 

WHERE invoices.invoiceId >=1
AND invoices.invoiceId <=1

If this is an example of what's auto-generated for you, I'd say you need a better generator.

Upvotes: 1

transilvlad
transilvlad

Reputation: 14532

Your queries look alright. I have had up to 10 joins with no problems on performance.

Some fun facts about MySQL performance:

  1. Always use the MySQL quotes. I was tasked to improve the performance of a messy query. First thing I did was arrange the code in a readable fashion and add the quotes. 10% spike in performance was the result.

  2. Always join by indexed numeric fields and never use two conditions in the join unless no other option exists cause it is a performance downer.

  3. In where conditions always add them in the order that will select the lest amount having the indexes first this can bring up to 99% boost in performance.

Just my two cents.

Upvotes: 1

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Hard to say really I doubt that example is too much of problem, though admittedly it's somewhat unweildy. Given what you are doing, the lengthy sql isn't that much of a problem as you are hiding it behind some hopefully more expressive presentation. I'd hesitate to put what amounts to an arbitary limit of number of relations you can express. If it turns out to be slow, then that's a schema change and as far as I can see, out of scope.

Upvotes: 0

Related Questions