Solving The N+1 Problem In PHP
Minimum price
Suggested price

Solving The N+1 Problem In PHP

About the Book

The web page was taking three hours to load.

Let that sink in for a moment: three hours.

The code backing the page was a reporting script written in PHP, attempting to build 40,000 entity objects from 2 million database rows. Indeed, sometimes the page never loaded at all. At least the client could tell when it failed, because a big ugly error popped up in the browser window. Unfortunately that was also around the three hour point. The client was understandably frustrated.

It turned out the cause was not that the database was unoptimized. It was not the number of rows being returned. It was not the number of objects being built.No, the cause was the high volume of queries being issued from the reporting script.

When I say "high volume", I mean two hundred thousand and one queries. These 200,000+ queries were used to build each root entity along with each of its five associated collections and entities, all as a result of how the script was assembling the entity objects. No wonder the script was was taking three hours to finish!

After discovering this, I was able to apply the techniques presented in this book to refactor how the entity objects were assembled. Over the course of a few days of refactoring I was able to reduce the load time from 3+ hours to just under 6 minutes. That's a 30x improvement, and while still slow by typical standards, it was much more bearable for the client. (Later refactorings improved the load time even further.)

The causes leading to the terrible performance of that reporting script have a name: The N+1 Problem. The problem goes by various aliases, such as the "1+N problem", "N+1 queries", "SELECT N+1", and so on, but they all refer to the same thing.

While I mention the N+1 problem more than once in Modernizing Legacy Applications in PHP, I did not address a solution directly. This book remedies that. Herein, we will discuss the specifics of the N+1 problem:

  • what the N+1 problem is,
  • how to diagnose and discover the problem in your application,
  • why the problem occurs, and
  • how to solve the problem in your codebase using plain old PHP (no ORMs needed!).

If your application code has database performance issues, or if you want to learn how to prevent creating an N+1 problem in your codebase, buy this book today!

About the Author

Paul M. Jones
Paul M. Jones

Paul M. Jones is an internationally recognized PHP expert who has worked as everything from junior developer to VP of Engineering in all kinds of organizations (corporate, military, non-profit, educational, medical, and others). Paul's latest open-source project is the Atlas Persistence Framework for PHP. Among his other accomplishments, Paul is the lead developer on Aura for PHP and Solar Framework, and the creator of the Savant template system. He has authored a series of authoritative benchmarks on dynamic framework performance, and was a founding contributor to the Zend Framework (the DB, DB_Table, and View components). Paul was a founding member of the PHP Framework Interoperability Group, where he shepherded the PSR-1 and PSR-2 recommendations, and was the primary author on the PSR-4 autoloader recommendation. He was also a member of the Zend PHP 5.3 Certification education advisory board. He blogs at In a previous career, Paul was an operations intelligence specialist for the US Air Force, and enjoys putting .308 holes in targets at 400 yards.

Paul M. Jones

Episode 20

Table of Contents

  • 1. Introduction
    • The Slow Reporting Script
    • Example Application
  • 2. Defining The N+1 Problem
    • The Problem By Example
    • The Problem Defined
    • Performance Implications
  • 3. Diagnosing N+1 Problems
    • Codebase Examination
    • Log/Profile Examination
    • Easier Query Examination
  • 4. Why Does The N+1 Problem Occur?
    • CRUD Is The Problem
    • BREAD Is The Answer
  • 5. Solving N+1 With A Single Query
    • Handling “Many-To-One” Relationships
    • Handling “One-To-One” Relationships
    • Combining “Many-To-One” and “One-To-One”
    • Handling “To-Many” Relationships
    • Single-Query Fails With “To-Many” Relationships
  • 6. Solving N+1 With Query-And-Stitch
    • Tradeoffs In Query-And-Stitch
    • Handling “One-To-Many” Relationships
    • Handling “Many-To-Many” Relationships
    • Handling “Many-To-One” Relationships
    • Handling “One-To-One” Relationships
    • Performance Implications
  • 7. Refactoring The Codebase To Remove N+1 Problems
    • Prerequisites
    • The Process
    • N+1 In A Single Location
    • N+1 Across Separated Locations
    • Test, Commit, Push, Notify QA
    • Do … While
  • 8. Common Questions
    • Can I Combine Single-Query With Query-And-Stitch?
    • Can I Use Sub-Selects Instead Of The IN() Operator?
    • Should I Use A Transaction With Query-And-Stitch?
    • Can I Just Use An ORM?
  • Appendix
    • SQL Schema
    • PHP Classes
  • Colophon
  • About the Author

The Leanpub 60-day 100% Happiness Guarantee

Within 60 days of purchase you can get a 100% refund on any Leanpub purchase, in two clicks.

See full terms

80% Royalties. Earn $16 on a $20 book.

We pay 80% royalties. That's not a typo: you earn $16 on a $20 sale. If we sell 5000 non-refunded copies of your book or course for $20, you'll earn $80,000.

(Yes, some authors have already earned much more than that on Leanpub.)

In fact, authors have earnedover $12 millionwriting, publishing and selling on Leanpub.

Learn more about writing on Leanpub

Free Updates. DRM Free.

If you buy a Leanpub book, you get free updates for as long as the author updates the book! Many authors use Leanpub to publish their books in-progress, while they are writing them. All readers get free updates, regardless of when they bought the book or how much they paid (including free).

Most Leanpub books are available in PDF (for computers) and EPUB (for phones, tablets and Kindle). The formats that a book includes are shown at the top right corner of this page.

Finally, Leanpub books don't have any DRM copy-protection nonsense, so you can easily read them on any supported device.

Learn more about Leanpub's ebook formats and where to read them

Write and Publish on Leanpub

You can use Leanpub to easily write, publish and sell in-progress and completed ebooks and online courses!

Leanpub is a powerful platform for serious authors, combining a simple, elegant writing and publishing workflow with a store focused on selling in-progress ebooks.

Leanpub is a magical typewriter for authors: just write in plain text, and to publish your ebook, just click a button. (Or, if you are producing your ebook your own way, you can even upload your own PDF and/or EPUB files and then publish with one click!) It really is that easy.

Learn more about writing on Leanpub