MySQL - nested inner joins make the query vey slow - solutions?

Sunny
November 25, 2023
211 views
0 votes
2 Answers

I have a query:

select count(*) from transaction_entry 
inner join inventory_item on inventory_item_id = inventory_item.id
inner join transaction on transaction_id = transaction.id
inner join product on inventory_item.product_id = product.id

when adding the last inner join, so to product table, the query becomes very slow – from 5s, to 40s, are there any ways to improve the performance of this nested join?
I have indexes for ids in those tables, so the solution should aim for something else than indexes.

Edit:
If I use those queries separetely, they seem to be very fast, the problem occurs when I try to join transaction table and inventory_item table together:

select count(*) from transaction_entry 
inner join inventory_item on inventory_item_id = inventory_item.id
inner join product on inventory_item.product_id = product.id

the above is very fast

select count(*) from transaction_entry 
inner join transaction on transaction_id = transaction.id
inner join inventory_item on inventory_item_id = inventory_item.id

the above is very slow

select count(*) from transaction_entry 
inner join transaction on transaction_id = transaction.id

the above again is very fast

The explain of the query:

select count(*) from transaction_entry 
inner join inventory_item on inventory_item_id = inventory_item.id
inner join transaction on transaction_id = transaction.id
inner join product on inventory_item.product_id = product.id

innodb_buffer_pool_size: 134217728

transaction_entry table is 994MB

transaction table is 504MB

inventory_item table is 23MB

product table is 24MB

and whole explain:

id|select_type|table            |partitions|type  |possible_keys                        |key               |key_len|ref                                    |rows |filtered|Extra                   |
--+-----------+-----------------+----------+------+-------------------------------------+------------------+-------+---------------------------------------+-----+--------+------------------------+
 1|SIMPLE     |inventory_item   |          |index |PRIMARY,product_id                   |product_id        |883    |                                       |73470|   100.0|Using where; Using index|
 1|SIMPLE     |product          |          |eq_ref|PRIMARY,idx_product_id               |PRIMARY           |114    |dbdev3.inventory_item.product_id       |    1|   100.0|Using index             |
 1|SIMPLE     |transaction_entry|          |ref   |FKABC21FD1AA992CED,FKABC21FD12EF4C7F4|FKABC21FD1AA992CED|115    |dbdev3.inventory_item.id               |   21|   100.0|Using where             |
 1|SIMPLE     |transaction      |          |eq_ref|PRIMARY                              |PRIMARY           |114    |dbdev3.transaction_entry.transaction_id|    1|   100.0|Using index             |  |

transaction_entry create table:

CREATE TABLE `transaction_entry` (
  `id` char(38) NOT NULL DEFAULT '',
  `version` bigint NOT NULL,
  `inventory_item_id` char(38) DEFAULT NULL,
  `quantity` int NOT NULL,
  `transaction_id` char(38) DEFAULT NULL,
  `comments` varchar(255) DEFAULT NULL,
  `transaction_entries_idx` int DEFAULT NULL,
  `bin_location_id` char(38) DEFAULT NULL,
  `product_id` char(38) DEFAULT NULL,
  `reason_code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FKABC21FD1AA992CED` (`inventory_item_id`),
  KEY `FKABC21FD12EF4C7F4` (`transaction_id`),
  KEY `FKABC21FD169DB749D` (`bin_location_id`),
  KEY `FKABC21FD1DED5FAE7` (`product_id`),
  CONSTRAINT `FKABC21FD12EF4C7F4` FOREIGN KEY (`transaction_id`) REFERENCES `transaction` (`id`),
  CONSTRAINT `FKABC21FD169DB749D` FOREIGN KEY (`bin_location_id`) REFERENCES `location` (`id`),
  CONSTRAINT `FKABC21FD1AA992CED` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory_item` (`id`),
  CONSTRAINT `FKABC21FD1DED5FAE7` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

transaction table create table:

CREATE TABLE `transaction` (
  `id` char(38) NOT NULL DEFAULT '',
  `version` bigint NOT NULL,
  `created_by_id` char(38) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `destination_id` char(38) DEFAULT NULL,
  `inventory_id` char(38) DEFAULT NULL,
  `last_updated` datetime NOT NULL,
  `source_id` char(38) DEFAULT NULL,
  `transaction_date` datetime NOT NULL,
  `transaction_type_id` char(38) DEFAULT NULL,
  `confirmed` bit(1) DEFAULT NULL,
  `confirmed_by_id` char(38) DEFAULT NULL,
  `date_confirmed` datetime DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `incoming_shipment_id` char(38) DEFAULT NULL,
  `outgoing_shipment_id` char(38) DEFAULT NULL,
  `updated_by_id` char(38) DEFAULT NULL,
  `transaction_number` varchar(255) DEFAULT NULL,
  `requisition_id` char(38) DEFAULT NULL,
  `order_id` char(38) DEFAULT NULL,
  `receipt_id` char(38) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK7FA0D2DE3265A8A9` (`confirmed_by_id`),
  KEY `FK7FA0D2DE217F5972` (`created_by_id`),
  KEY `FK7FA0D2DE1E2B3CDC` (`destination_id`),
  KEY `FK7FA0D2DE72A2C5B4` (`inventory_id`),
  KEY `FK7FA0D2DE828481AF` (`source_id`),
  KEY `FK7FA0D2DEB3FB7111` (`transaction_type_id`),
  KEY `FK7FA0D2DE426DD105` (`updated_by_id`),
  KEY `FK7FA0D2DE5DE9E374` (`requisition_id`),
  KEY `FK7FA0D2DE5F12AFED` (`incoming_shipment_id`),
  KEY `FK7FA0D2DEB80B3233` (`outgoing_shipment_id`),
  KEY `FK7FA0D2DED08EDBE6` (`order_id`),
  KEY `FK7FA0D2DEF7076438` (`receipt_id`),
  CONSTRAINT `FK7FA0D2DE1E2B3CDC` FOREIGN KEY (`destination_id`) REFERENCES `location` (`id`),
  CONSTRAINT `FK7FA0D2DE217F5972` FOREIGN KEY (`created_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK7FA0D2DE3265A8A9` FOREIGN KEY (`confirmed_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK7FA0D2DE426DD105` FOREIGN KEY (`updated_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK7FA0D2DE5DE9E374` FOREIGN KEY (`requisition_id`) REFERENCES `requisition` (`id`),
  CONSTRAINT `FK7FA0D2DE5F12AFED` FOREIGN KEY (`incoming_shipment_id`) REFERENCES `shipment` (`id`),
  CONSTRAINT `FK7FA0D2DE72A2C5B4` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`id`),
  CONSTRAINT `FK7FA0D2DE828481AF` FOREIGN KEY (`source_id`) REFERENCES `location` (`id`),
  CONSTRAINT `FK7FA0D2DEB3FB7111` FOREIGN KEY (`transaction_type_id`) REFERENCES `transaction_type` (`id`),
  CONSTRAINT `FK7FA0D2DEB80B3233` FOREIGN KEY (`outgoing_shipment_id`) REFERENCES `shipment` (`id`),
  CONSTRAINT `FK7FA0D2DED08EDBE6` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`),
  CONSTRAINT `FK7FA0D2DEF7076438` FOREIGN KEY (`receipt_id`) REFERENCES `receipt` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

inventory_item create table table:

CREATE TABLE `inventory_item` (
  `id` char(38) NOT NULL DEFAULT '',
  `version` bigint NOT NULL,
  `date_created` datetime NOT NULL,
  `last_updated` datetime NOT NULL,
  `lot_number` varchar(255) DEFAULT NULL,
  `product_id` char(38) DEFAULT NULL,
  `expiration_date` datetime DEFAULT NULL,
  `comments` varchar(255) DEFAULT NULL,
  `lot_status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_id` (`product_id`,`lot_number`),
  CONSTRAINT `FKFE019416DED5FAE7` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

product create table:

CREATE TABLE `product` (
  `id` char(38) NOT NULL DEFAULT '',
  `version` bigint NOT NULL,
  `category_id` char(38) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `description` mediumtext,
  `last_updated` datetime NOT NULL,
  `name` varchar(255) NOT NULL,
  `product_code` varchar(255) DEFAULT NULL,
  `unit_of_measure_id` char(38) DEFAULT NULL,
  `cold_chain` bit(1) DEFAULT NULL,
  `manufacturer` varchar(255) DEFAULT NULL,
  `manufacturer_code` varchar(255) DEFAULT NULL,
  `ndc` varchar(255) DEFAULT NULL,
  `upc` varchar(255) DEFAULT NULL,
  `unit_of_measure` varchar(255) DEFAULT NULL,
  `created_by_id` char(38) DEFAULT NULL,
  `updated_by_id` char(38) DEFAULT NULL,
  `default_uom_id` char(38) DEFAULT NULL,
  `brand_name` varchar(255) DEFAULT NULL,
  `vendor` varchar(255) DEFAULT NULL,
  `vendor_code` varchar(255) DEFAULT NULL,
  `package_size` int DEFAULT NULL,
  `model_number` varchar(255) DEFAULT NULL,
  `manufacturer_name` varchar(255) DEFAULT NULL,
  `vendor_name` varchar(255) DEFAULT NULL,
  `active` bit(1) DEFAULT NULL,
  `controlled_substance` bit(1) DEFAULT NULL,
  `essential` bit(1) DEFAULT NULL,
  `hazardous_material` bit(1) DEFAULT NULL,
  `lot_control` bit(1) DEFAULT NULL,
  `serialized` bit(1) DEFAULT NULL,
  `price_per_unit` decimal(19,4) DEFAULT NULL,
  `reconditioned` bit(1) DEFAULT NULL,
  `product_type_id` char(38) DEFAULT NULL,
  `abc_class` varchar(255) DEFAULT NULL,
  `cost_per_unit` decimal(19,4) DEFAULT NULL,
  `gl_account_id` char(38) DEFAULT NULL,
  `lot_and_expiry_control` bit(1) DEFAULT NULL,
  `product_family_id` char(38) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_code` (`product_code`),
  KEY `FKED8DCCEFEF4C770D` (`category_id`),
  KEY `FKED8DCCEF217F5972` (`created_by_id`),
  KEY `FKED8DCCEFEEB2908D` (`default_uom_id`),
  KEY `FKED8DCCEF426DD105` (`updated_by_id`),
  KEY `FKED8DCCEFABD88AC6` (`product_type_id`),
  KEY `fk_product_gl_account` (`gl_account_id`),
  KEY `fk_product_product_family` (`product_family_id`),
  KEY `idx_product_id` (`id`),
  CONSTRAINT `fk_product_gl_account` FOREIGN KEY (`gl_account_id`) REFERENCES `gl_account` (`id`),
  CONSTRAINT `fk_product_product_family` FOREIGN KEY (`product_family_id`) REFERENCES `product_group` (`id`),
  CONSTRAINT `FKED8DCCEF217F5972` FOREIGN KEY (`created_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FKED8DCCEF426DD105` FOREIGN KEY (`updated_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FKED8DCCEFABD88AC6` FOREIGN KEY (`product_type_id`) REFERENCES `product_type` (`id`),
  CONSTRAINT `FKED8DCCEFEEB2908D` FOREIGN KEY (`default_uom_id`) REFERENCES `unit_of_measure` (`id`),
  CONSTRAINT `FKED8DCCEFEF4C770D` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

Answers

first you need to add index in tables

CREATE INDEX idx_inventory_item_id ON transaction_entry (inventory_item_id);
CREATE INDEX idx_transaction_id ON transaction_entry (transaction_id);
CREATE INDEX idx_product_id ON inventory_item (product_id);ry

Try this query

SELECT COUNT(*) AS total_entries
FROM transaction_entry TE
WHERE EXISTS (
   SELECT 1
   FROM inventory_item II
   INNER JOIN transaction T ON TE.transaction_id = T.id
   INNER JOIN product P ON II.product_id = P.id
   WHERE TE.inventory_item_id = II.id
);

- RickJames
- November 24, 2023 at 11:39 pm
- 0 votes
0
When it was slow, it was probably very I/O-bound.
```
innodb_buffer_pool_size: 134217728
```
is an old default. Raise it to about 70% of available RAM so that more data can be cached in RAM.

Also…
- char(38) — smells like some sort of UUID. This can add to the disk space and sluggishness. (However, increasing the buffer_pool setting will probably mask these issues until the data gets much bigger.) See also: UUIDs
- NULLable columns in UNIQUE keys seems ‘wrong’.
- As for "I don’t know if this matters,… it never uses the PRIMARY key, …" , note that
```
  UNIQUE KEY `product_id` (`product_id`,`lot_number`)
```
  is a BTree that implicitly contains contains id. Hence the query that needs only id and product_id is actually wise to use this structure instead of the larger BTree that contains all the columns of the table.
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.

MySQL – nested inner joins make the query vey slow – solutions?

Answers