0

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
4
  • The query doesn't make sense. It simply selects a row with 1 for each row in transaction_items with transaction_items.transaction_item_id = 10577645
    – Akina
    Dec 3, 2021 at 16:45
  • Having more than 2 UNIQUE (or PRIMARY) keys is usually a no-no. Re-think the indexes on transaction_items.
    – Rick James
    Dec 3, 2021 at 17:55
  • @Akina I should’ve been clearer about this, but I’ve removed everything from the query that isn’t relevant to my question, including the actual SELECT statement. I’m also aware it’s pointless to GROUP BY a primary key selection; the reason I’ve posted is that I don’t understand why using a string condition changes the query-plan
    – 568ml
    Dec 3, 2021 at 20:20
  • We already know that the posted query is a simplification, but based on what we can actually see, I'd be inclined to think that the plan changes because you change the predicate (from transaction_item_id IN(...) to transaction_item_id = ...), not because you change the data type.
    – mustaccio
    Dec 3, 2021 at 21:13

1 Answer 1

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 .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.