A product list often needs related information for every row: the number of people who favorited the product, whether the current viewer is one of them, and perhaps a few names or avatars. The tempting implementation loads the products first and then runs one or two queries per product. With 50 products, that becomes 101 database round trips. This is the N+1 query problem.
My original solution avoided those round trips by constructing JSON with GROUP_CONCAT(). Modern MySQL provides native JSON aggregate functions, so manual quoting and delimiter handling are no longer necessary. The more important improvement is to decide how much related data the list actually needs. Returning every user for every product merely replaces many small queries with one potentially enormous response.
Model Favorites as a Relationship
Products and users form a many-to-many relationship. The junction table prevents duplicate favorites and supports lookups from either direction.
CREATE TABLE product (
product_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
PRIMARY KEY (product_id)
) ENGINE=InnoDB;
CREATE TABLE user_account (
user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
display_name VARCHAR(100) NOT NULL,
avatar_url VARCHAR(500) NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB;
CREATE TABLE product_favorite (
product_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
favorited_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY KEY (product_id, user_id),
KEY ix_product_favorite_user (user_id, product_id),
KEY ix_product_favorite_recent (product_id, favorited_at DESC, user_id),
CONSTRAINT fk_product_favorite_product
FOREIGN KEY (product_id) REFERENCES product (product_id)
ON DELETE CASCADE,
CONSTRAINT fk_product_favorite_user
FOREIGN KEY (user_id) REFERENCES user_account (user_id)
ON DELETE CASCADE
) ENGINE=InnoDB; The primary key serves product-to-user membership checks. The reverse index serves a user's favorites, and the recent index supports a small, deterministic preview for each product.
Prefer Counts for a Product List
Most list views do not need every favoriting user. Fetch one page of products, aggregate counts only for that page, and join the current viewer's relationship separately:
WITH selected_products AS (
SELECT product_id, name
FROM product
WHERE product_id > ?
ORDER BY product_id
LIMIT ?
),
favorite_counts AS (
SELECT
pf.product_id,
COUNT(*) AS favorite_count
FROM product_favorite AS pf
JOIN selected_products AS sp
ON sp.product_id = pf.product_id
GROUP BY pf.product_id
)
SELECT
sp.product_id,
sp.name,
COALESCE(fc.favorite_count, 0) AS favorite_count,
(vf.user_id IS NOT NULL) AS viewer_favorite
FROM selected_products AS sp
LEFT JOIN favorite_counts AS fc
ON fc.product_id = sp.product_id
LEFT JOIN product_favorite AS vf
ON vf.product_id = sp.product_id
AND vf.user_id = ?
ORDER BY sp.product_id; The first parameter is the last product ID from the previous page, the second is a bounded page size, and the third is the authenticated viewer ID. Bind all values through the database API. Keyset pagination keeps the work stable as later pages are requested and avoids the shifting cost of a large OFFSET.
Products without favorites remain in the result because the aggregates are left-joined to the selected products. The viewer join uses the composite primary key and does not depend on parsing the aggregated data.
Add a Bounded User Preview
If the interface displays a few recent users, rank favorites per selected product and aggregate only the first entries. The preview limit belongs in the query, not in PHP after fetching every relationship.
WITH selected_products AS (
SELECT product_id, name
FROM product
WHERE product_id > ?
ORDER BY product_id
LIMIT ?
),
ranked_favorites AS (
SELECT
pf.product_id,
u.user_id,
u.display_name,
u.avatar_url,
ROW_NUMBER() OVER (
PARTITION BY pf.product_id
ORDER BY pf.favorited_at DESC, pf.user_id
) AS preview_position
FROM product_favorite AS pf
JOIN selected_products AS sp
ON sp.product_id = pf.product_id
JOIN user_account AS u
ON u.user_id = pf.user_id
),
favorite_counts AS (
SELECT
pf.product_id,
COUNT(*) AS favorite_count
FROM product_favorite AS pf
JOIN selected_products AS sp
ON sp.product_id = pf.product_id
GROUP BY pf.product_id
),
favorite_previews AS (
SELECT
product_id,
JSON_ARRAYAGG(
JSON_OBJECT(
'id', user_id,
'name', display_name,
'avatarUrl', avatar_url
)
) AS favorite_users
FROM ranked_favorites
WHERE preview_position <= ?
GROUP BY product_id
)
SELECT
sp.product_id,
sp.name,
COALESCE(fc.favorite_count, 0) AS favorite_count,
COALESCE(fp.favorite_users, JSON_ARRAY()) AS favorite_users,
(vf.user_id IS NOT NULL) AS viewer_favorite
FROM selected_products AS sp
LEFT JOIN favorite_counts AS fc
ON fc.product_id = sp.product_id
LEFT JOIN favorite_previews AS fp
ON fp.product_id = sp.product_id
LEFT JOIN product_favorite AS vf
ON vf.product_id = sp.product_id
AND vf.user_id = ?
ORDER BY sp.product_id; The preview parameter might be 3 or 5, depending on the interface. The count still describes all favorites, while favorite_users contains only the preview. MySQL does not guarantee element order for JSON_ARRAYAGG(); if display order is significant, sort the small decoded preview by a returned position or fetch preview rows as a second batched result set.
Decode Native JSON in PHP
Keep SQL values and query structure separate. With PDO, bind the page cursor, limits, and viewer ID instead of interpolating session data into SQL:
<?php
$statement = $pdo->prepare($sql);
$statement->bindValue(1, $afterProductId, PDO::PARAM_INT);
$statement->bindValue(2, $pageSize, PDO::PARAM_INT);
$statement->bindValue(3, $previewSize, PDO::PARAM_INT);
$statement->bindValue(4, $viewerId, PDO::PARAM_INT);
$statement->execute();
$products = [];
while ($product = $statement->fetch(PDO::FETCH_ASSOC)) {
$product['favorite_count'] = (int) $product['favorite_count'];
$product['viewer_favorite'] = (bool) $product['viewer_favorite'];
$product['favorite_users'] = json_decode(
$product['favorite_users'],
true,
512,
JSON_THROW_ON_ERROR
);
$products[] = $product;
} JSON_OBJECT() handles string escaping correctly, including quotes, control characters, and Unicode. That is the decisive advantage over concatenating JSON syntax manually. JSON_THROW_ON_ERROR also prevents malformed data from becoming an unnoticed null in PHP.
When Two Queries Are Better
One query is not automatically faster. A practical alternative is:
- Fetch the product page with counts and viewer state.
- Fetch preview users for all product IDs in that page with one additional set-based query.
That is two round trips regardless of page size, not N+1. It avoids repeating product columns across relationship rows, preserves an explicit row order, and can be easier to cache. Native JSON aggregation is most useful when a nested document is genuinely the desired transport format, not as a goal by itself.
Keep the Response Bounded
Never aggregate an unbounded relationship into a list endpoint. Large arrays consume database memory, network bandwidth, PHP memory, and JSON decoding time. They may also expose more user data than the interface needs. Paginate products, cap previews, return the total count separately, and provide a dedicated paginated endpoint when somebody asks to see all favorites.
Inspect the query with EXPLAIN ANALYZE using representative data. Verify that only the selected product range and its favorite rows are read. The useful optimization is not merely reducing the visible query count; it is bounding the total work while preserving correct empty products, counts, viewer state, and JSON encoding.