<xml.lt>

After starting using an ORM and enjoying the benefits it brings, you might soon run into so-called SELECT N+1 problem if you don't use it properly, probably even without noticing it.

SELECT N+1 occurs when you retrieve a collection of objects from database via ORM, and then iterate it accessing the object properties of collection members. For example (we will use Propel):

$posts = PostPeer::doSelect(new Criteria());

foreach ($posts as $post)
{
	$author = $post->getAuthor();
	print $author->getName(); // do something with $author
}

Here we have Post and Author as classes, and each Post has it's own Author (this of course has to be specified as a foreign key in the database schema).
Now, when you access the author of every post in a loop, Propel loads it from the database with a separate SELECT query. And if there were N posts, you get a total of N such queries, plus 1 to load the posts in the first place — thus SELECT N+1.

While such code would work, it is far from optimal, since there is no need to SELECT from database in a loop to get authors of every post. Frequent database queries is one of the biggest hits on backend performance, and SELECT N+1 is causing it but often overlooked. If you are not sure if you have run into this problem, it is easiest to check your database query logs for excessive amounts of alike-looking SELECTs.

The problem is solved using eager fetching, which means that main and related objects are loaded in one go. In other words, when posts are loaded, author for each of them has to be instantiated at the same time, and not later when it is accessed.
The solution is implemented using JOINs and SELECTing data from related tables into one dataset. Processing it into objects (or “populating” objects, in Propel terms) needs little more memory and logic, but that is negligible compared to the overall performance gain. With Propel, it is achieved simply by specifying a different table-specific peer method for loading posts:

$posts = PostPeer::doSelectJoinAuthor(new Criteria());