JOINs are evil, part II
It’s odd that I’m suddenly seeing this advice everywhere. Ten years ago, when I was learning about databases, I never heard anyone suggest that JOINs are evil:
#4: Avoid joins
As your tables get bigger and bigger joins cannot be done in memory and spill over to disk. Once that happens your performance is gone. For smaller tables it doesn’t matter but as tables get bigger avoid joining when you can. Try using unions, subselects, or denormalization instead. If you have two big tables that are 1-to-1 that you are joining a lot combine them into one wide table.
August 12th, 2009 at 12:10 pm
I’m currently experiencing a ‘Join best avoided’ development, and yes this suggestion feels strange, but it becomes natural when working with huge datasets.
For some years now, frameworks and ‘write no SQL’ abstraction tools have been massively adopted. I think it has contributed at some laziness and shortcuts for database developments in general. What is good for a ‘2 lines of Ruby’ blog is not appropriate for a big scale app !
August 13th, 2009 at 12:51 pm
Working with the Symfony framework, and the Propel ORM, I’ve learned the best way to handle JOINs is to use database views. You can add the view to the Symfony schema, and then generate model classes, at which point Symfony/Propel treat the view as if it is an actual table (for selects) and you’ve got a set of classes that can get the data for you, through the ORM.
August 13th, 2009 at 1:07 pm
Yes, but views are applicable since all data are hosted on the same node.
The idea behind avoiding joins is to come over cross-server joins, which are not possible in MySQL.
August 13th, 2009 at 5:06 pm
Ah, I see. Thanks for sharing that. My knowledge is far too concentrated on MySql, with all its limits.