Work around the 61 table JOIN limit in MySQL by nesting subqueries within each other

Work around the 61 table JOIN limit in MySQL by nesting subqueries within each other

July 21, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Work around the 61 table JOIN limit in MySQL by nesting subqueries within each other

Nesting Subqueries for Overcoming MySQL’s 61 Table Join Limit

As a developer working with large datasets and complex joins, you might find yourself hitting MySQL’s infamous limit of 61 table joins in a single query. While this restriction can be frustrating, there are elegant workarounds available. One such technique involves nesting subqueries within each other to effectively bypass the join limit.

Understanding the Problem

Let’s take a look at an example involving Magento and its EAV (Entity-Attribute-Value) structure. In this scenario, you’re trying to retrieve detailed product information by joining multiple tables:

  1. catalog_product_entity - The primary products table
  2. catalog_product_entity_int - Stores integer attributes for each product
  3. eav_attribute - Defines the available attributes
  4. eav_attribute_option and eav_attribute_option_value - Store option values for select/option list type attributes

The query attempts to join these tables together to retrieve various attribute values (like brand, color, size) associated with each product:

SELECT cpe.entity_id
, brand_int.value as brand_int, brand.value as brand
, color_int.value as color_int, color.value as color
, size_int.value as size_int, size.value as size

FROM catalog_product_entity as cpe

LEFT JOIN catalog_product_entity_int as brand_int
ON (cpe.entity_id = brand_int.entity_id AND brand_int.attribute_id = 2)
LEFT JOIN eav_attribute_option as brand_option
ON (brand_option.attribute_id = 2 AND brand_int.value = brand_option.option_id)
LEFT JOIN eav_attribute_option_value as brand
ON (brand_option.option_id = brand.option_id)

...
-- Similar joins for color and size attributes

;

While this approach is straightforward, it quickly reaches the join limit when dealing with a large number of attributes. The math shows that a mere 21 integer attributes would exceed the limit before accounting for other data types like varchar, text, or decimal.

The Subquery Solution

To overcome this limitation without compromising query performance, you can employ nested subqueries. Instead of joining all tables in one go, break them down into smaller, manageable chunks that each perform a single join operation between two tables (or a table and a subquery).

Here’s an example of how the nested syntax might look:

SELECT p.entity_id,
       b.value as brand_name,
       c.value as color_name,
       s.value as size_name
FROM catalog_product_entity AS p

-- Subquery for brand attribute
LEFT JOIN (
  SELECT entity_id, value option_id
  FROM catalog_product_entity_int
  WHERE attribute_id = 2
) AS brand
ON p.entity_id = brand.entity_id

-- Subquery for color attribute
LEFT JOIN (
  SELECT entity_id, value option_id
  FROM catalog_product_entity_int
  WHERE attribute_id = 3
) AS color
ON p.entity_id = color.entity_id

-- Subquery for size attribute
LEFT JOIN (
  SELECT entity_id, value option_id
  FROM catalog_product_entity_int
  WHERE attribute_id = 4
) AS size
ON p.entity_id = size.entity_id

;

In this nested approach, each subquery focuses on a single attribute and its corresponding join operation. By breaking down the problem into smaller pieces that can be solved independently, you avoid hitting the join limit.

Benefits of Nested Subqueries

  1. Ease of writing: The code becomes more manageable as each query concerns only two tables or one table and a subquery at most.
  2. Improved readability: Smaller, focused queries are easier to understand and debug than a single, complex join operation involving many tables.
  3. Scalability: As your dataset grows, you can easily add more attributes by simply adding another nested subquery without worrying about the join limit.

Conclusion

Nesting subqueries is an effective way to work around MySQL’s 61 table join limit when dealing with complex joins in EAV-based systems like Magento. By breaking down the problem into smaller, manageable pieces and focusing on single-join operations within each subquery, you can retrieve detailed product information without hitting the limitation.

Remember that while this technique is powerful, it’s essential to monitor query performance as your dataset grows. The use of nested subqueries might introduce slight overhead compared to a single join operation with proper indexing.

For more insights into optimizing MySQL queries and working with large datasets, I recommend checking out Laravel Company’s blog for in-depth articles on best practices and performance tuning techniques.

Catalog Database API Errors

Our network