Raw PHP and MySQL Eager Loading

Working with complicated and joined data sets can be tricky for MySQL database performance. For example, What if you need to load multiple authors and their books from a database. How would you work around the N+1 problem and avoid unnecessary queries to the database without an expensive join statement? The answer is quite simple.

Use eager loading.

Eager Loading

Eager loading is simply fetching records preemptively. For example, when the author records are requested you can aggregate their ID. Then, you can query for only the books used by those authors beforehand.

SELECT * FROM `authors` LIMIT 3;

If this query returns the authors of the IDs 1,2,3 you can save queries on books by running just one select statement,

SELECT * FROM `books` WHERE `author_id` IN ( 1,2,3 );

PHP: Solutions

There are two great ways to work with eager loading in PHP.

  1. Using a handy ORM.
  2. Manually using PHP's PDO and your own code.

ORM

With an ORM such as Eloquent in Laravel eager loading is very simple. Laracasts has a great video explaining the situation and then covers the solution and there is a more recent video if you have an account.

PDO

There are times you do not want the overhead of an ORM. You might also want to build eager loading yourself. In these cases, you will need to use PHP's PDO class.

Let's say we have 10 authors. If you queried their books without eager loading you would run 11 SQL queries.

query("SELECT * FROM `authors` LIMIT 10;");

// Query books 10 times!
foreach($authors as $author) {
var_dump($author);
$id = (int) $author['id'];
$books = $pdo->query("SELECT * FROM `books` WHERE `author_id` = {$id}");
foreach($books as $book) {
var_dump($book);
}
}

The code is nice and brief but at the expense of speed. 11 queries are not ideal. Imagine the speed if 100 authors are returned. Ouch!

If you use eager loading you can have the same net result with only 2 queries.

query("SELECT * FROM `authors` LIMIT 10;");

// Setup eager list of IDs
$ids = [];
foreach($authors as $author) {
$ids[] = (int) $author['id'];
}
$author_ids = implode(',', $ids);

// Get all books and group them
$grouped_books = [];
$all_books = $pdo->query("SELECT * FROM `books` WHERE `author_id` IN ( {$author_ids} )");
foreach($all_books as $book) {
$grouped_books[$book['author_id']][] = $book;
}

// Final loop
foreach ($authors as $author) {
var_dump($author);
var_dump($grouped_books[ $author['id'] ]);
}

Author: Kevin

Howdy, I’m Kevin Dees. I’m the founder of Robojuice. It is a web strategy company focused on delivering a refined experience to partners. I’m also the creator of TypeRocket for WordPress.