Magento 2: Fix MySQL ‘SELECT Would Examine More Than MAX_JOIN_SIZE Rows’ Error When Reindexing

databaseindexingmagento-2.1magento2

I'm getting the following error when running the catalog_product_price indexer from the CLI tool (bin/magento indexer:reindex catalog_product_price):

SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT
would examine more than MAX_JOIN_SIZE rows; check your WHERE and use
SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay,
query was:

DELETE `index_price`
FROM `catalog_product_index_price` AS `index_price`
LEFT JOIN `catalog_product_index_price_idx` AS `ip_tmp` ON
    index_price.entity_id = ip_tmp.entity_id
    AND index_price.website_id = ip_tmp.website_id
WHERE (ip_tmp.entity_id IS NULL)

The website has 8,419 products, and 4 websites.

Truncating the price index tables works for a while, however the error does come back after a few days.

Has anyone seen this issue before? Is it simply a case of tweaking the MySQL parameters? Or is this indicative of a larger problem?

Best Answer

Indeed you can tweak Mysql parameters.

For example, with a default AWS Maria DB, I have these values :

show variables where Variable_name = 'max_join_size';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| max_join_size | 18446744073709551615 |
+---------------+----------------------+

MariaDB [(none)]> show variables where Variable_name = 'SQL_BIG_SELECTS';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| sql_big_selects | ON    |
+-----------------+-------+
Related Topic