Search code examples
mysqlsqljoinexplain

fix Using index, Using temporary, Using filesort

i have such 3 tables. Im trying to Left Join them

    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

here is the structure of tables 

    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;

And SELECT EXPLAIN shows me

explain-img t_newsrelations is intermediate table. Table t_categories contains categories and subcategories linked by parent_id column. Each item from t_news can be a member of more than one subcategories thats why they linked through t_newsrelations

how to optimise a query? Why it shows Using index, Using temporary, Using filesort?

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

    You can't eliminate the temp table and filesort in this query, given the tables you have, because you're sorting on columns from multiple tables. Optimizing sorting means using an index so that the query fetches rows in the order you want them. But there's no way in MySQL to create an index that spans multiple tables.

    The only way to fix this is to denormalize until all three columns are in a single table, and create one index over the three columns. But denormalization comes with its own downsides.