Different search keys for different versions of mariadb

user_9353319 0
Viewed 30

I have a query running on mariadb 10.3 very quickly - but when it moves across to mariadb 10.7, it takes up to 6 minutes!

The query is:

SELECT
    products.code AS productCode,
    products. `name` AS productDescription,
    products.unit_of_measure,
    product_types.fg_or_rp AS productType,                
    product_batches.is_blocked,
    CASE WHEN product_batches.expiry_date < NOW() AND products.is_batch_tracked = 1 THEN
        1
    ELSE
        0
    END AS is_expired,
    sum(pallet_items.quantity) AS quantity_soh
FROM
    products
    INNER JOIN product_batches ON product_batches.product_id = products.id
    INNER JOIN pallet_items ON pallet_items.product_batch_id = product_batches.id
    INNER JOIN pallets ON pallets.id = pallet_items.pallet_id
    INNER JOIN storage_locations ON storage_locations.id = pallets.current_location_id
    INNER JOIN product_types ON products.product_type_id = product_types.id
    INNER JOIN stock_locations ON stock_locations.id = storage_locations.stock_location_id
WHERE
    stock_locations.stock_group_id in(
        SELECT
            id FROM stock_groups
        WHERE
            stock_groups.include_in_stock_on_hand = 1)
GROUP BY
    products.code, products. `name`, unit_of_measure, product_batches.is_blocked, 
    CASE WHEN product_batches.expiry_date < NOW() AND products.is_batch_tracked = 1 THEN
        1
    ELSE
        0
    END, product_types.fg_or_rp, product_batches.is_blocked
ORDER BY
    products.code

When I use the "Explain" function, I see that Mariadb 10.7 has selected different keys for the one section to mariadb 10.3, shown below:

Mariadb 10.3 (fast) uses the following key for the pallet_items table: pallet_items_pallet_id_foreign

whereas the Mariadb 10.7 (slow) uses the following: pallet_items_product_batch_id_foreign

As requested, please see full "explain" of the query:

Table of the "Explain" output

Also, here are the "Show table" {tablenames} for each of the tables:

pallet_types

pallet_types    "CREATE TABLE `pallet_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"

storage_locations

storage_locations   "CREATE TABLE `storage_locations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entry_x` int(11) DEFAULT NULL,
  `entry_y` int(11) DEFAULT NULL,
  `entry_z` int(11) DEFAULT NULL,
  `exit_x` int(11) DEFAULT NULL,
  `exit_y` int(11) DEFAULT NULL,
  `exit_z` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `storage_function_id` bigint(20) unsigned DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `max_quantity` int(10) unsigned DEFAULT NULL,
  `is_multi_product` tinyint(1) DEFAULT 0,
  `stock_location_id` bigint(20) unsigned DEFAULT 2,
  `client_location_code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `storage_locations_storage_function_id_foreign` (`storage_function_id`),
  KEY `storage_locations_stock_location_id_foreign` (`stock_location_id`),
  CONSTRAINT `storage_locations_stock_location_id_foreign` FOREIGN KEY (`stock_location_id`) REFERENCES `stock_locations` (`id`),
  CONSTRAINT `storage_locations_storage_function_id_foreign` FOREIGN KEY (`storage_function_id`) REFERENCES `storage_functions` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=770 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"

stock_locations

stock_locations "CREATE TABLE `stock_locations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stock_group_id` bigint(20) unsigned NOT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stock_locations_stock_group_id_foreign` (`stock_group_id`),
  CONSTRAINT `stock_locations_stock_group_id_foreign` FOREIGN KEY (`stock_group_id`) REFERENCES `stock_groups` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"

stock_groups

stock_groups    "CREATE TABLE `stock_groups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `include_in_stock_on_hand` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"

pallet_statuses

pallet_statuses "CREATE TABLE `pallet_statuses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"

pallet_ledger_entries

pallet_ledger_entries   "CREATE TABLE `pallet_ledger_entries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pallet_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `product_batch_id` bigint(20) unsigned DEFAULT NULL,
  `quantity` decimal(14,4) NOT NULL,
  `event_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `soh_pallet` decimal(14,4) NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pallet_ledger_entries_product_id_foreign` (`product_id`),
  KEY `pallet_ledger_entries_product_batch_id_foreign` (`product_batch_id`),
  KEY `pallet_ledger_entries_pallet_id_foreign` (`pallet_id`),
  KEY `pallet_ledger_entries_user_id_foreign` (`user_id`),
  CONSTRAINT `pallet_ledger_entries_pallet_id_foreign` FOREIGN KEY (`pallet_id`) REFERENCES `pallets` (`id`),
  CONSTRAINT `pallet_ledger_entries_product_batch_id_foreign` FOREIGN KEY (`product_batch_id`) REFERENCES `product_batches` (`id`),
  CONSTRAINT `pallet_ledger_entries_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `pallet_ledger_entries_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=231839 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"

products

products    "CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_type_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_batch_tracked` tinyint(1) NOT NULL DEFAULT 1,
  `metric_of_weight` decimal(10,3) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `unit_of_measure` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cost` decimal(20,10) DEFAULT NULL,
  `unit_ean` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shrink_ean` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `case_ean` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shipping_weight` decimal(10,2) DEFAULT NULL,
  `shipping_weight_unit` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `net_weight` decimal(10,2) DEFAULT NULL,
  `net_weight_unit` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `size` decimal(10,2) DEFAULT NULL,
  `size_unit` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `production_line` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shelf_life_days` int(11) DEFAULT NULL,
  `exclude_from_receiving_stack` tinyint(1) NOT NULL DEFAULT 0,
  `ignore_batch_no_check` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `products_product_type_id_foreign` (`product_type_id`),
  CONSTRAINT `products_product_type_id_foreign` FOREIGN KEY (`product_type_id`) REFERENCES `product_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=738 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"

product_types

product_types   "CREATE TABLE `product_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `fg_or_rp` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"

Any thoughts on how I can make the query select the correct (faster) key in 10.7? Many thanks for the help so far!

mariadb sql
1 Answers

One of my colleagues suggested the following edit which did the trick:

FORCE INDEX (pallet_items_pallet_id_foreign) when added to the following:

LEFT JOIN pallet_items FORCE INDEX (pallet_items_pallet_id_foreign) ON pallet_items.pallet_id = pallets.id

solved the problem!

user_93533190
Related