I have some case where I need to write a single SQL query but inside that query, I also need to get some data on another table. Let me give some visualization.

The customers table schema

I have some case where I need to write a single SQL query but inside that query, I also need to get some data on another table. Let me give some visualization.

The customers table schema

The addresses table schema

The example case is: I need to get each customer along with their default addresses, if I run this query

SELECT
  id,
  name,
  (
    SELECT 
      address
    FROM addresses
    WHERE
      customer_id = customers.id AND
      is_default = 1 LIMIT 1
  ) AS default_address 
FROM customers;

Would it be possible for this query to cause N+1 problem because for each customer it also needs to fetch their default address?

I have write those kind of query on my projects for many times and it just occurs to me "Would it be possible for those query to cause N+1"?

Would expect anyone to give me some explanation about it, is the N+1 query can be happening or not. Thank you!

• sql
• mysql
• select-n-plus-1

• 2 I have only seen the term N+1 in the context of Hibernate. Note that using LIMIT without ORDER BY is fairly non sensical. – Tim Biegeleisen Commented Feb 17 at 3:55
• if I have to assume N is number of clients (correlated query execution) + 1 (main query). And yes, this is (will be) the execution plan. This task is solved with join – mr mcwolf Commented Feb 17 at 4:01
• Well you can't be sure because the database engine could easily (and would hopefully) use a join instead of a sub-query. So really you have to check your execution plan to see what is happening. – Dale K Commented Feb 17 at 4:47
• Does some code provides that in addresses table only one row per customer_id contains is_default <> 0? Does that code provides strictly one such row (the presence of such row)? Why this mark which is a customer entity attribute is stored not in customers table but in addresses table? – Akina Commented Feb 17 at 6:56

2 Answers 2

This is a potential N+1 problem. The subquery inside SELECT executes once per customer. If the database doesn't optimize it well, this means that for N customers, the subquery runs N times (once per row)

Instead of a subquery, use a LEFT JOIN

SELECT 
    c.id,
    c.name,
    a.address AS default_address
FROM customers c
LEFT JOIN addresses a 
    ON c.id = a.customer_id AND a.is_default = 1;

It fetches all data in one go instead of running a subquery per row. If customer_id is indexed, the join will be optimized. Also for a large dataset JOIN queries are more efficient than the subquery

• N+1 refers specifically to executing N+1 separate queries.
• The example correlated subquery case is a single SQL query.

With no proper indexing on the tables, MySQL could possibly run multiple table scans which can be inefficient, but it's still not technically N+1.

MySQL has EXPLAIN which can show how a query will be optimized.

A cleaner approach, but not necessarily more efficient:

SELECT c.id, c.name, a.address AS default_address
FROM customers c
LEFT JOIN addresses a ON c.id = a.customer_id AND a.is_default = 1