gpt4 book ai didi

mysql - 修复使用索引,使用临时,使用文件排序

转载 作者:行者123 更新时间:2023-11-29 01:57:55 25 4
gpt4 key购买 nike

我有这样的 3 个表。我想离开加入他们

    SELECT `t`.`title` AS `category_title`,`t`.`id` AS `category_id`, `st`.`title` AS
`subcategory_title`, `st`.`id` AS `subcategory_id`, `st`.`parent_id` AS
`subcategory_parent`, `n`.`title` AS `news_title`,`n`.`id` AS `news_id` FROM
`t_categories` `t` LEFT JOIN t_categories AS `st` ON `st`.`parent_id`=t.`id` LEFT JOIN
t_newsrelations AS `nr` ON `nr`.`category_id`=st.`id` LEFT JOIN t_news AS `n` ON
`n`.`id`=nr.`news_id` WHERE `t`.`enabled` = 1 AND `n`.`enabled` = 1 AND `n`.`type`!=1 AND
`n`.`type`!=5 ORDER BY `t`.`position`,`st`.`position`,`n`.`position` ASC

表格结构

    CREATE TABLE IF NOT EXISTS `t_categories` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(11) NOT NULL,
`title` tinytext NOT NULL,
`position` tinyint(4) unsigned NOT NULL DEFAULT '0',
`type` tinyint(1) unsigned NOT NULL,
`enabled` tinyint(1) unsigned NOT NULL DEFAULT '1',
UNIQUE KEY `id` (`id`),
KEY `type` (`type`),
KEY `parent_id` (`parent_id`),
KEY `enabled` (`enabled`),
KEY `id_parent_position_enabled` (`id`,`parent_id`,`position`,`enabled`),
KEY `position` (`position`),
KEY `parent_id_2` (`parent_id`,`enabled`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;


CREATE TABLE IF NOT EXISTS `t_news` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` tinytext NOT NULL,
`m_title` tinytext NOT NULL,
`url` varchar(2000) NOT NULL,
`keywords` text NOT NULL,
`description` text NOT NULL,
`body` longtext NOT NULL,
`position` tinyint(4) unsigned NOT NULL DEFAULT '0',
`type` tinyint(1) unsigned NOT NULL,
`city_id` int(4) NOT NULL,
`quickmenu_enabled` tinyint(1) unsigned NOT NULL DEFAULT '0',
`quickmenu` text NOT NULL,
`enabled` tinyint(1) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `position` (`position`),
KEY `type` (`type`),
KEY `city_id` (`city_id`),
KEY `url` (`url`(333)),
KEY `quickmenu_enabled` (`quickmenu_enabled`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;


CREATE TABLE IF NOT EXISTS `t_newsrelations` (
`category_id` int(11) NOT NULL,
`news_id` int(11) unsigned NOT NULL,
KEY `category_id` (`category_id`),
KEY `news_id` (`news_id`),
KEY `category_id_2` (`category_id`,`news_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

然后 SELECT EXPLAIN 显示给我

explain-imgt_newsrelations 是中间表。表 t_categories 包含由 parent_id 列链接的类别和子类别。 t_news 中的每一项都可以是多个子类别的成员,这就是为什么它们通过 t_newsrelations 链接

如何优化查询?为什么显示Using index、Using temporary、Using filesort?

最佳答案

ORDER BY `t`.`position`,`st`.`position`,`n`.`position` ASC

鉴于您拥有的表,您不能在此查询中删除临时表和文件排序,因为您要对多个表中的列进行排序。优化排序意味着使用索引,以便查询按照您希望的顺序获取行。但是在 MySQL 中无法创建跨多个表的索引。

解决这个问题的唯一方法是非规范化,直到所有三列都在一个表中,然后在这三列上创建一个索引。但非规范化也有其自身的缺点。

关于mysql - 修复使用索引,使用临时,使用文件排序,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/23228966/

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