Database peformance with joins

Question

Possible Duplicate:
When and why are database joins expensive?

My company has assigned me a job of improving DB performance. Earlier developer has written a query having 5 innerjoins.

What is the number of inner joins which can be used without hurting performance ?

Answer 1

JOINs can perform better than alternatives, in specific situations. And it's not unusual to see that many joins in a normalized database.

JOINs aren't always necessary -- if there aren't references outside of the JOIN for the table, EXISTS or IN will perform better. For example:

SELECT a.column
  FROM TABLE_A a 
  JOIN TABLE_B b ON b.col = a.col

...vs:

SELECT a.column
  FROM TABLE_A a 
 WHERE EXISTS(SELECT NULL 
                FROM TABLE_B b 
               WHERE b.col = a.col)

If the joins are necessary for the resultset, then assess the situation:

• Are there indexes on the foreign keys (the columns used to for the JOIN criteria)?

• Are any of the join criteria requiring functions for JOINs to work. IE:

  JOIN table x ON DATE(x.col) = y.column
  

  ...because such cases would render an index on x.col useless

• Is the data type of the foreign keys as narrow as possible, because the less bytes used to store the value means faster database performance. IE: VARCHAR(4) vs INT

Conclusion

It's plausible that denormalizing data can be a performance benefit but it's the very last option to consider after lots of review & testing.