Why does an integer comparison result in no temporary index in MariaDB?

Question

I am running a query on a table (transaction_items, c. 12 million rows) that has a many-to-many relationship with another table, and I wish to group transaction_items by this relationship.

To do this, my query:

1. Contains a sub-query over the many-to-many relationship's junction table (transaction_item_defects, c. 12 million rows), which it groups by the transaction_item_id key, and concatenates the relation's key (product_defect_id)
2. Left-joins this subquery
3. Groups the outer-query by the product_defect_id concatenation

This query is generated by an ORM. I have encountered a case where the outer-query has a WHERE condition over transaction_item_id, in which the argument is supplied as an integer. Under such conditions, as far as I can tell the query-planner does not use an index when joining the derived sub-query to the outer-query — the performance of this is terrible, with the query taking in excess of a minute.

I've investigated further, and have discovered that if I change the argument for the condition on transaction_item_id to a string, the query-planner instead generates a temporary index, which I believe it uses to join the derived sub-query to the outer-query. In this case the query is far more performant, taking less than a second.

My question: Why does changing the type from integer to string, when performing a comparison against an integer column, cause this change in the execution-plan?

Please note that I understand my example may seem a bit contrived — I have pared the query generated by the ORM down to the essential elements that reproduce the change in query plan. I'm open to and grateful for any incidental advice on how I can employ a better strategy to achieve my aim, but my main concern is the question above: why does the argument type change the behaviour (in what, to me, is a counter-intuitive way)?

ORM query (integer condition; non-performant)

EXPLAIN SELECT 1
FROM
    `transaction_items`
    LEFT JOIN (
        SELECT
            `transaction_item_defects`.`transaction_item_id`,
            GROUP_CONCAT(DISTINCT `product_defect_id`) AS `defects`
        FROM
            `transaction_item_defects`
        GROUP BY
            `transaction_item_defects`.`transaction_item_id`) `transaction_item_defects` ON `transaction_items`.`transaction_item_id` = `transaction_item_defects`.`transaction_item_id`
WHERE
    `transaction_items`.`transaction_item_id` IN(10577645)

ORM query result:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	transaction_items	ref	PRIMARY,transaction_item_valid_start,transaction_item_validity	transaction_item_valid_start	4	const	3	Using where; Using index
1	PRIMARY		ALL					11039311	Using where; Using join buffer (flat, BNL join)
2	DERIVED	transaction_item_defects	index		transaction_item_id	12		11039311	Using where; Using index

Modified query (string condition; performant)

# ORM EXPLAIN
EXPLAIN SELECT 1
FROM
    `transaction_items`
    LEFT JOIN (
        SELECT
            `transaction_item_defects`.`transaction_item_id`,
            GROUP_CONCAT(DISTINCT `product_defect_id`) AS `defects`
        FROM
            `transaction_item_defects`
        GROUP BY
            `transaction_item_defects`.`transaction_item_id`) `transaction_item_defects` ON `transaction_items`.`transaction_item_id` = `transaction_item_defects`.`transaction_item_id`
WHERE
    `transaction_items`.`transaction_item_id` = '10577645'

Modified query result

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	transaction_items	ref	PRIMARY,transaction_item_valid_start,transaction_item_validity	transaction_item_valid_start	4	const	3	Using where; Using index
1	PRIMARY		ref	key0	key0	5	docker.transaction_items.transaction_item_id	2
2	LATERAL DERIVED	transaction_item_defects	ref	transaction_item_id	transaction_item_id	4	docker.transaction_items.transaction_item_id	1	Using where; Using index

Table schemata

CREATE TABLE `transaction_items` (
  `transaction_item_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `legacy_transaction_item_type` enum('inventory_item','transaction_item') DEFAULT NULL,
  `legacy_transaction_item_id` mediumint(8) unsigned DEFAULT NULL,
  `type` enum('order','inventory','appraisal') NOT NULL,
  `fidelity` enum('contiguous','pooled','batch') NOT NULL,
  `valid_start` timestamp(6) NOT NULL DEFAULT current_timestamp(6),
  `valid_end` timestamp(6) NOT NULL DEFAULT '2038-01-19 03:14:07.999999',
  `log_request_id` int(10) unsigned DEFAULT NULL,
  `reference_id` mediumint(8) unsigned NOT NULL,
  `cross_reference_id` mediumint(8) unsigned DEFAULT NULL,
  `transaction_id` mediumint(8) unsigned DEFAULT NULL,
  `inventory_location_id` mediumint(8) unsigned DEFAULT NULL,
  `origin_id` int(11) unsigned DEFAULT NULL,
  `price` decimal(8,2) DEFAULT NULL,
  `notes` varchar(1000) DEFAULT NULL,
  `instance_definition` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`instance_definition`)),
  PERIOD FOR `valid_period` (`valid_start`, `valid_end`),
  PRIMARY KEY (`transaction_item_id`,`valid_period` WITHOUT OVERLAPS),
  UNIQUE KEY `transaction_item_valid_start` (`transaction_item_id`,`valid_start`) USING BTREE,
  UNIQUE KEY `transaction_item_validity` (`transaction_item_id`,`valid_start`,`valid_end`) USING BTREE,
  KEY `type` (`type`),
  KEY `log_request_id` (`log_request_id`),
  KEY `reference_id` (`reference_id`),
  KEY `cross_reference_id` (`cross_reference_id`),
  KEY `transaction_id` (`transaction_id`),
  KEY `inventory_location_id` (`inventory_location_id`),
  KEY `origin_id` (`origin_id`),
  KEY `reference_location` (`type`,`reference_id`,`inventory_location_id`),
  KEY `reference_transaction` (`reference_id`,`transaction_id`,`type`),
  KEY `fidelity` (`fidelity`) USING BTREE,
  CONSTRAINT `transaction_items_ibfk_2` FOREIGN KEY (`reference_id`) REFERENCES `references` (`reference_id`) ON UPDATE CASCADE,
  CONSTRAINT `transaction_items_ibfk_3` FOREIGN KEY (`cross_reference_id`) REFERENCES `references` (`reference_id`) ON UPDATE CASCADE,
  CONSTRAINT `transaction_items_ibfk_4` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`transaction_id`) ON UPDATE CASCADE,
  CONSTRAINT `transaction_items_ibfk_5` FOREIGN KEY (`inventory_location_id`) REFERENCES `inventory_locations` (`inventory_location_id`) ON UPDATE CASCADE,
  CONSTRAINT `transaction_items_ibfk_6` FOREIGN KEY (`origin_id`) REFERENCES `transaction_items` (`transaction_item_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING

CREATE TABLE `transaction_item_defects` (
  `transaction_item_id` int(11) unsigned NOT NULL,
  `product_defect_id` tinyint(3) unsigned NOT NULL,
  UNIQUE KEY `transaction_item_id` (`transaction_item_id`,`product_defect_id`),
  UNIQUE KEY `product_defect_id` (`product_defect_id`,`transaction_item_id`),
  CONSTRAINT `transaction_item_defects_ibfk_2` FOREIGN KEY (`product_defect_id`) REFERENCES `product_defects` (`product_defect_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING

Answer 1

(This is a guess...)

These are redundant:

  UNIQUE KEY `transaction_item_valid_start` (`transaction_item_id`,`valid_start`) USING BTREE,
  UNIQUE KEY `transaction_item_validity` (`transaction_item_id`,`valid_start`,`valid_end`) USING BTREE,

Changing them to these may help:

  UNIQUE KEY `transaction_item_valid_start` (`valid_start`, `transaction_item_id`) USING BTREE,
  INDEX `transaction_item_validity` (`transaction_item_id`,`valid_start`,`valid_end`) USING BTREE,

Note that I flipped the columns in the first one. This may keep it from being picked while maintaining the uniqueness constraint.

The second one was changed to a plain INDEX, since there is no need for it to be UNIQUE since the other one is a subset.

PERIOD FOR is a brand new feature in MariaDB 10.5; perhaps it has some birthing pains. If you don't get satisfaction here, file a bug report at jira.mariadb.org .