There are MariaDB 10.4.14 with max_join_size = 300M and the coin InnoDB table with ~150,000 records inside.
A simple enough query produces MAX_JOIN_SIZE error:
SELECT * FROM coin z -- USE INDEX(PRIMARY)
WHERE z.id IN (5510, 5511, 5512 /* more item IDs up to 250 */)
AND z.currency_id IN (8, 227)
AND z.distribution_id IN (1, 2);
Error Code: 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
But the same query with index hint works well. It does not matter which index is in hit ["PRIMARY", "currency_id_idx", "distribution_id"] or even issue_date_idx It works even without indexes USE INDEX() at all.
What could be wrong here? Why doesn't the query work without hint?
By the way, this query works well on MariaDB 10.3.24 and doesn't work on 10.5.5
OPTIMIZE TABLE coin; -- didn't help
Table DDL and query EXPLAIN
CREATE TABLE `coin` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(90) NOT NULL DEFAULT '',
`country_id` smallint(5) unsigned NOT NULL DEFAULT 0,
`currency_id` smallint(5) unsigned NOT NULL DEFAULT 0,
`distribution_id` tinyint(3) unsigned NOT NULL,
`issue_date` date NOT NULL DEFAULT '0000-00-00',
-- and other, total 29 fields
PRIMARY KEY (`id`),
KEY `issue_date_idx` (`issue_date`),
KEY `currency_id_idx` (`currency_id`),
KEY `distribution_id` (`distribution_id`),
-- and other, total 21 indices for other fields which don't use currency_id or distribution_id
CONSTRAINT `coin_ibfk_4` FOREIGN KEY (`currency_id`) REFERENCES `currency` (`id`),
CONSTRAINT `coin_ibfk_11` FOREIGN KEY (`distribution_id`) REFERENCES `distribution` (`id`),
-- and other, total 13 CONSTRAINTs
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
EXPLAIN FORMAT=JSON -- for SELECT above without index hint
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "z",
"access_type": "range",
"possible_keys": ["PRIMARY", "currency_id_idx", "distribution_id"],
"key": "PRIMARY",
"key_length": "3",
"used_key_parts": ["id"],
"rows": 50,
"filtered": 100,
"attached_condition": "z.`id` in (5510,5511,5512, /* ... total 100 */) and z.currency_id in (8,227) and z.distribution_id in (1,2)"
}
}
}
The above SELECT works without index hint with max possible MAX_JOIN_SIZE value and doesn't with max value - 1:
SET MAX_JOIN_SIZE=18446744073709551615 -- this works
SET MAX_JOIN_SIZE=18446744073709551614 -- this doesn't work
