gpt4 book ai didi

sql - 优化查询以删除 "Using where; Using temporary; Using filesort"

转载 作者:行者123 更新时间:2023-11-29 07:10:33 31 4
gpt4 key购买 nike

我有一个简单的 SQL 查询,我正在尝试对其进行优化以删除“使用位置;使用临时;使用文件排序”。

这是表格:

CREATE TABLE `special_offers` (
`so_id` int(11) NOT NULL auto_increment,
`so_lid` int(11) NOT NULL,
`so_product_id` int(11) NOT NULL,
`so_bonus_product` int(11) NOT NULL,
`so_reverse_relate` tinyint(1) NOT NULL default '0',
`so_discount_amount` varchar(6) NOT NULL,
`so_start` date NOT NULL default '0000-00-00',
`so_expiry` date NOT NULL default '0000-00-00',
`so_active` tinyint(1) NOT NULL,
`so_archived` tinyint(4) NOT NULL default '0',
`so_added` datetime NOT NULL,
PRIMARY KEY (`so_id`),
KEY `so_archived` (`so_archived`),
KEY `so_active` (`so_active`),
KEY `so_start` (`so_start`),
KEY `so_expiry` (`so_expiry`),
KEY `so_product_id` (`so_product_id`),
KEY `so_bonus_product` (`so_bonus_product`),
KEY `so_lid` (`so_lid`)
) ENGINE=MyISAM AUTO_INCREMENT=65610 DEFAULT CHARSET=latin1

这是查询:

SELECT `so_id` , `so_lid` , `so_bonus_product` , `so_product_id`
FROM `special_offers`
WHERE `so_archived` = '0'
AND `so_active` = '1'
AND (
`so_start` <= CURDATE( )
OR `so_start` = '0000-00-00'
)
AND (
`so_expiry` >= CURDATE( )
OR `so_expiry` = '0000-00-00'
)
GROUP BY `so_lid`

一个解释:

mysql> EXPLAIN SELECT `so_id` , `so_lid` , `so_bonus_product` , `so_product_id` FROM `special_offers` WHERE `so_archived` = '0' AND `so_active` = '1' AND ( `so_start` <= CURDATE( ) OR `so_start` = '0000-00-00' ) AND ( `so_expiry` >= CURDATE( ) OR `so_expiry` = '0000-00-00' ) GROUP BY `so_lid`;
+----+-------------+-------------------+------+------------------------------------------+-------------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------+------------------------------------------+-------------+---------+-------+------+----------------------------------------------+
| 1 | SIMPLE | special_offers | ref | so_archived,so_active,so_start,so_expiry | so_archived | 1 | const | 7684 | Using where; Using temporary; Using filesort |
+----+-------------+-------------------+------+------------------------------------------+-------------+---------+-------+------+----------------------------------------------+

最佳答案

(so_archived, so_active, so_lid, so_start, so_end) 上创建复合索引

关于sql - 优化查询以删除 "Using where; Using temporary; Using filesort",我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4782998/

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