使用filesort和临时优化查询

Question

任何人都可以帮助我优化此查询。它需要一些时间来运行：

的su_pref表有近90万行

SELECT p.mykey, 
     p.merchant_name, 
     m.merchant_url, 
     p.name, 
     p.description, 
     p.image_url, 
     p.deep_link, 
     p.rrp_price, 
     p.display_price, 
     c.category, 
     p.su_parent_name 
FROM su_pref p # 
     INNER JOIN su_categoryrefs cr ON p.mykey = cr.mykey 
     INNER JOIN su_categories c ON cr.id = c.id 
     INNER JOIN su_merchants m ON p.merchant_id=m.id 
WHERE 
    cr.id =36 
ORDER BY p.date_created DESC LIMIT 0,20 

这是我所得到的，当我使用EXPLAIN

我真的很努力理解如何使用EXPLAIN优化查询，所以任何帮助将不胜感激。

以下是表格定义。该su_pref表是相当大的，所以我剥夺某些领域出

CREATE TABLE `su_pref` (
    `mykey` varchar(50) NOT NULL, 
    `merchant_id` smallint(3) default NULL, 
    `merchant_name` varchar(50) NOT NULL default '', 
    `brand` varchar(50) default NULL, 
    `merchantproductkey` varchar(50) default NULL, 
    `upc` varchar(15) default NULL, 
    `name` varchar(255) NOT NULL default '', 
    `description` varchar(2500) NOT NULL default '', 
    `short_description` varchar(500) default NULL, 
    `thumb_url` varchar(500) default NULL, 
    `image_url` varchar(500) default NULL, 
    `deep_link` varchar(1000) default NULL, 
    `merchant_link` varchar(255) default NULL, 
    `rrp_price` decimal(11,2) default NULL, 
    `display_price` decimal(11,2) default NULL, 
    `delivery_cost` decimal(11,2) default NULL, 
    `price_range` tinyint(1) default NULL, 
    `keywords` varchar(500) default NULL, 
    `su_parent_name` enum('Women','Men','') NOT NULL, 
    `date_created` date default NULL, 
    `date_modified` datetime default NULL, 
    `wp_featured` varchar(1) default NULL, 
    `hp_featured` varchar(1) default NULL, 
    `published` varchar(1) default NULL, 
    `in_todays_feed` varchar(1) default NULL, 
    PRIMARY KEY (`mykey`), 
    KEY `merchant_id` (`merchant_id`), 
    KEY `date_created` (`date_created`), 
    FULLTEXT KEY `product_search` 
       (`name`,`description`,`short_description`, 
       `keywords`, `product_type`,`colour`,`style`, 
       `material`,`datafeed_category_name`, 
       `datafeed_subcategory_name`, 
       `brand`,`merchant_name`), 
    FULLTEXT KEY `name` (`name`,`datafeed_category_name`, 
         `datafeed_subcategory_name`,`product_type`,`keywords`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

-

CREATE TABLE `su_categories` (
    `id` mediumint(9) NOT NULL, 
    `category` varchar(100) NOT NULL, 
    `parent_id` tinyint(4) NOT NULL, 
    `update_query` varchar(3000) default NULL, 
    `url` varchar(150) default NULL, 
    `last_update` datetime default NULL, 
    PRIMARY KEY (`id`,`category`,`parent_id`), 
    KEY `parent_id` (`parent_id`), 
    KEY `category_id` (`id`), 
    FULLTEXT KEY `category_name` (`category`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

-

CREATE TABLE `su_categoryrefs` (
    `mykey` varchar(255) NOT NULL, 
    `id` smallint(4) NOT NULL, 
    PRIMARY KEY (`mykey`,`id`), 
    KEY `id` (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

-

CREATE TABLE `su_merchants` (
    `id` mediumint(9) NOT NULL, 
    `merchant_name` varchar(40) NOT NULL, 
    `merchant_url` varchar(40) default NULL, 
    `merchant_website` varchar(40) default NULL, 
    `merchant_description` varchar(2000) default NULL, 
    `merchant_featured` varchar(1) default NULL, 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `merchant_name` (`merchant_name`) 
) ENGINE=MyISAM AUTO_INCREMENT=128 DEFAULT CHARSET=latin1 

预先感谢您

Answer 1

无法判断哪些表中的每个不合格列都正在返回，或JOIN谓词中的“merchant_id”正在从哪个表中引用，或者引用了ORDER BY子句中的哪个表date_created。 （我们可以做出一些猜测，但他们只是说，猜测。

第一步，真的，是限定所有这些列引用与任一表名，或者最好是表的别名。

此外，格式化的语句，使凡人可以理解它帮了不少忙。

你有合格列引用格式化声明帮助和模式定义的包容也有帮助。（我会得到我的回答更新）

SELECT p.mykey 
    , p.merchant_name 
    , merchant_url 
    , `name` 
    , description 
    , image_url 
    , deep_link 
    , rrp_price 
    , display_price 
    , category 
    , su_parent_name 
FROM su_pref p 
JOIN su_categoryrefs cr ON cr.mykey = p.mykey 
JOIN su_categories c ON c.id  = cr.id 
JOIN su_merchants m  ON m.id  = p.merchant_id 
WHERE cr.id = 36 
ORDER BY date_created DESC LIMIT 0,20 

作为一般的经验法则，希望在连接谓词中引用具有前导列的索引。通常，这些是PRIMARY KEY和所有外键。此外，如果是“覆盖索引”（包含查询中引用的所有列的索引），则会在EXPLAIN输出中看到“使用索引”;这意味着查询在索引中得到满足，而无需引用数据块。

EXPLAIN似乎显示被引用的“colour_id”索引，但它并不明显指出哪个列包含在该索引中。 （EXPLAIN PLAN输出图像太难阅读了。）

---

这里有一些建议：

确保你有一个指标

ON su_categories (id, category)  -- you do 
ON su_merchants (id, merchant_url) -- never mind this one, it's a small table 
ON su_categoryrefs (id, mykey) 

我假设在su_categoryrefs表中的列id是一个外键引用su_categories.id，而不是表的主键。 （这看起来像是一个关系表，它可以解决su_categories和'su_preferences`之间的多对多关系，但我只是猜测。