Alex Rivera | Logout

How costly are JOINs in SQL? And/or, what's the trade off between performance and normalization?

Asked 2011-04-24T22:19:23.820
22

I've found a similar thread but it doesn't really capture the essence of what I'm trying to ask - so I've created a new thread.

I know there is a trade-off between normalization and performance, and I'm wondering what's the best practice for drawing that line? In my particular situation, I have a messaging system that has three distinct tables: messages_threads (overarching message holder), messages_recipients (who is involved), and messages_messages (the actual messages + timestamps).

In order to return the "inbox" view, I have to left join the messages_threads table, users table, and pictures tables to the messages_recipients tables in order to get the information to populate the view (profile picture, sender name, thread id)... and I've still got add a join to messages to retrieve the text from the last message in order to display a "preview" of the last message to the user.

My question is: How costly are JOINS in SQL to performance? I could, for instance, store the sender's name (which I have to left join from users to retrieve) under a field in the messages_threads table called "sendername" - but in terms of normalization I've always been taught to avoid data redundancy?

Where do you draw the line? Or am I overestimating how performance-hampering SQL joins are?

mysql sql performance join
Edit
Report

2 Answers

3

There is no simple answer to that question. Joins costs vary greatly depending on available indexes, number of records and many other factors. AFAIR in MySQL there are at least a couple of join strategies that are sorted from best to worst case scenario.

In practise you need to make the schema according to the general rules regarding the data security - so do normalize your database when it's needed.

Denormalization should happen only if you have a real performance problem and there is no other way to solve it (eg. adding an index, changing parameters, rewriting the query, ...) and should be based on deep analysis of the problem.

answered 2011-04-24T22:32:30.653
1

It's not possible, or useful, to answer a question about how costly joins are.

A join is just a command in the SQL query, what the database does with that join is something completely different. What's expensive in a query is things like table scans, where the database has to read an entire table to locate some data. A query with ten joins on tables where there are useful indexes can be much faster than a query on a single table without any useful indexes.

Three or four joins in a query is certainly not any reason to de-normalise the tables to try to improve performance. As comparison; for our web site we are using a de-normalised table to read from, because we would need about 40 joins to gather the data that we need.

answered 2011-04-24T22:45:41.083

Your Answer