source

다른 버전의 mariadb에 대한 다른 검색 키

manysource 2023. 10. 27. 22:01

다른 버전의 mariadb에 대한 다른 검색 키

mariadb 10.3에서 매우 빠르게 쿼리가 실행됩니다. 그러나 mariadb 10.7로 이동하면 최대 6분이 걸립니다!

질문은 다음과 같습니다.

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

"설명" 기능을 사용하면 아래와 같이 mariadb 10.7이 mariadb 10.3에 대한 한 섹션의 키를 다르게 선택한 것을 볼 수 있습니다.

Mariaadb 10.3(빠른)은 pallet_items 테이블에 다음 키를 사용합니다: pallet_items_pallet_id_foreign

반면에 마리아드비 10.7(느림)은 다음을 사용합니다: pallet_items_product_batch_id_foreign

요청에 따라 쿼리에 대한 전체 "설명"을 참조하십시오.

Table of the "Explain" output

또한 각 테이블에 대한 "테이블 표시" {tablename}이(가) 다음과 같습니다.

팔레트_타입

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   "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 "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    "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 "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"

팔레트_ledger_

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    "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   "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"

10.7에서 쿼리가 올바른 (더 빠른) 키를 선택하도록 하는 방법에 대한 생각이 있습니까?지금까지 도와주셔서 감사합니다!

제 동료 중 한 명이 다음과 같은 편집을 제안했습니다.

FORCE INDEX (pallet_items_pallet_id_foreign)다음 항목에 추가되는 경우:

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

문제를 해결했습니다!

언급URL : https://stackoverflow.com/questions/73289011/different-search-keys-for-different-versions-of-mariadb

'source' 카테고리의 다른 글

매개 변수가 있는 지시어에서 컨트롤러 함수 호출  (0) 2023.11.01
jQuery의 .hide()와 표시할 CSS 설정의 차이: 없음  (0) 2023.11.01
호출기능의 파일명, 회선번호, 함수명 출력 - C Prog  (0) 2023.10.27
데이터 프레임에서 발생하는 문자열 모두 바꾸기  (0) 2023.10.27
입력 버튼에서 외곽선 테두리를 제거하는 방법  (0) 2023.10.27

'source'의 다른글

  • 현재글다른 버전의 mariadb에 대한 다른 검색 키

관련글

댓글

티스토리툴바