gpt4 book ai didi

mysql - 长时间运行的查询

转载 作者:行者123 更新时间:2023-11-29 06:53:38 24 4
gpt4 key购买 nike

1.下面的查询大约需要 49 秒才能执行。2.我们的目标是需要在1到2秒内得到结果。3.查询有索引并且正在使用4.如何避免长时间执行。5.这是一个简单的查询6.如果可能的话帮我重写查询。

query:
select cppm.* from cat_ctlg_product_product_map cppm, cat_product_product_map ppm where cppm.product_product_map_id = ppm.product_product_map_id and ppm.product_id = 2585682 and cppm.catalog_id in ( 2136359, 2136371);

Explain plan:
+----+-------------+-------+-------+------------------------------------+---------------------------+---------+-------+--------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------------------------+---------------------------+---------+-------+--------+--------------------------------+
| 1 | SIMPLE | ppm | ref | PRIMARY,idx_3342,idx_5419 | idx_3342 | 4 | const | 1 | Using index |
| 1 | SIMPLE | cppm | range | idx_3472,fkey_cat_ctlg_produc_4100 | fkey_cat_ctlg_produc_4100 | 4 | NULL | 135334 | Using where; Using join buffer |
+----+-------------+-------+-------+------------------------------------+---------------------------+---------+-------+--------+--------------------------------+
2 rows in set (0.00 sec)

Table structures:
mysql> show create table cat_ctlg_product_product_map\G
*************************** 1. row ***************************
Table: cat_ctlg_product_product_map
Create Table: CREATE TABLE `cat_ctlg_product_product_map` (
`row_mod` datetime DEFAULT NULL,
`row_create` datetime DEFAULT NULL,
`product_product_map_id` int(11) NOT NULL,
`catalog_id` int(11) NOT NULL,
UNIQUE KEY `idx_3472` (`product_product_map_id`,`catalog_id`),
KEY `fkey_cat_ctlg_produc_4100` (`catalog_id`),
CONSTRAINT `fkey_cat_ctlg_produc_4100` FOREIGN KEY (`catalog_id`) REFERENCES `cat_catalogs` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fkey_cat_ctlg_produc_5415` FOREIGN KEY (`product_product_map_id`) REFERENCES `cat_product_product_map` (`product_product_map_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql> show create table cat_product_product_map\G
*************************** 1. row ***************************
Table: cat_product_product_map
Create Table: CREATE TABLE `cat_product_product_map` (
`row_mod` datetime DEFAULT NULL,
`row_create` datetime DEFAULT NULL,
`product_product_map_id` int(11) NOT NULL,
`owner_catalog_id` int(11) NOT NULL,
`parent_product_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`precedence` int(11) DEFAULT '100',
`is_default` int(11) DEFAULT NULL,
`product_product_type` enum('cross-sell','skuoption','up-sell','addon','works','kit','autocross') COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (`product_product_map_id`),
KEY `idx_3342` (`product_id`,`product_product_type`),
KEY `idx_5251` (`parent_product_id`,`product_product_type`,`product_id`),
KEY `idx_5419` (`product_product_map_id`,`parent_product_id`,`product_id`),
KEY `fkey_cat_product_pro_4229` (`owner_catalog_id`),
KEY `cat_product_product_map_n1` (`parent_product_id`,`product_product_type`,`product_product_map_id`,`precedence`),
CONSTRAINT `fkey_cat_product_pro_3617` FOREIGN KEY (`product_id`) REFERENCES `cat_products` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fkey_cat_product_pro_4229` FOREIGN KEY (`owner_catalog_id`) REFERENCES `cat_catalogs` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fkey_cat_product_pro_4362` FOREIGN KEY (`parent_product_id`) REFERENCES `cat_products` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

最佳答案

使用连接

select cppm.* from cat_ctlg_product_product_map cppm
INNER JOIN cat_product_product_map ppm ON (cppm.product_product_map_id = ppm.product_product_map_id AND cppm.catalog_id in ( 2136359, 2136371))
WHERE ppm.product_id = 2585682;

关于mysql - 长时间运行的查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14069935/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com