How to optimize a MySQL query with EXPLAIN showing 'using temporary; using filesort'

Question

I have a rather slow query that I'd like to optimize. EXPLAIN shows 'using temporary; using filesort'. I tried a few solutions and, doing without an ORDER BY, even managed to get rid of the 'using filesort'. But is there a way to avoid 'using temporary; using filesort' entirely, without sacrificing the ORDER BY?

This is my query:

SELECT `tags`.`name`,
       `tags`.`tag_id`,
       COUNT(*) AS `qty_products`
    FROM `products_subsubcategories`
        JOIN `products_tags` ON `products_subsubcategories`.`product_id` = `products_tags`.`product_id`
        JOIN `products` ON `products_subsubcategories`.`product_id` = `products`.`product_id`
        JOIN `tags` ON `products_tags`.`tag_id` = `tags`.`tag_id`
    WHERE     `products_subsubcategories`.`subsubcategory_id` = 55
          AND `tags`.`type` = 'brand'
          AND `products`.`dont_display` = 0
    GROUP BY `tags`.`tag_id`
    ORDER BY `tags`.`order`,
             `tags`.`name`;

The subsubcategory 55 is dynamic user input.

This is the EXPLAIN result:

id  select_type     table                       type    possible_keys               key                 key_len     ref                                         rows    filtered    Extra   
1   SIMPLE          products_subsubcategories   ref     PRIMARY,subsubcategory_id   subsubcategory_id   4           const                                       3982    100.00      Using temporary; Using filesort
1   SIMPLE          tags                        ALL     PRIMARY,type                NULL                NULL        NULL                                        679     78.94       Using where; Using join buffer
1   SIMPLE          products                    eq_ref  PRIMARY,dont_display        PRIMARY             4           mbb.products_subsubcategories.product_id    1       100.00      Using where
1   SIMPLE          products_tags               eq_ref  PRIMARY,tag_id              PRIMARY             8           mbb.products.product_id,mbb.tags.tag_id     1       100.00      Using where; Using index

(When I replace ORDER BY ... by ORDER BY NULL, the 'using filesort' disapperars. I could sort the result with PHP afterwards, although it's more convenient with MySQL, of course ...)

My tables look like this:

CREATE TABLE IF NOT EXISTS `products_subsubcategories` (
  `position` smallint(5) unsigned NOT NULL DEFAULT '0',
  `product_id` int(10) unsigned NOT NULL,
  `subsubcategory_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`product_id`,`subsubcategory_id`),
  KEY `subsubcategory_id` (`subsubcategory_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `products_tags` (
  `product_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`product_id`,`tag_id`),
  KEY `tag_id` (`tag_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `products` (
  `article_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `date` date DEFAULT NULL,
  `delivery_time` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `dont_display` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ean` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `image_error` tinyint(1) NOT NULL DEFAULT '0',
  `image_is_downloaded` tinyint(1) NOT NULL DEFAULT '0',
  `image_url` varchar(400) COLLATE utf8_unicode_ci NOT NULL,
  `image_url_170_134` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `image_url_original_size` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `is_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_duplicate` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_not_associated_to_category` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_not_associated_to_subcategory` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_not_associated_to_subsubcategory` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `last_association` datetime DEFAULT NULL,
  `last_completion_by_ean` datetime DEFAULT NULL,
  `matching_age` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `matching_brand` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `matching_category` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `matching_color` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `matching_gender` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `matching_keywords` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `matching_main_category` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `matching_size` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `matching_subcategory` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `matching_subsubcategory` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `old_price` decimal(7,2) unsigned NOT NULL DEFAULT '0.00',
  `price` decimal(7,2) unsigned NOT NULL DEFAULT '0.00',
  `product_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_list_id` int(10) unsigned NOT NULL DEFAULT '0',
  `qty_overall_clicks` int(10) unsigned NOT NULL DEFAULT '0',
  `shipping` decimal(7,2) unsigned NOT NULL DEFAULT '0.00',
  `shop_url` varchar(400) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `vendor_id` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`),
  KEY `article_number` (`article_number`),
  KEY `dont_display` (`dont_display`),
  KEY `ean` (`ean`),
  KEY `is_deleted` (`is_deleted`),
  KEY `is_duplicate` (`is_duplicate`),
  KEY `is_not_associated_to_category` (`is_not_associated_to_category`),
  KEY `is_not_associated_to_subcategory` (`is_not_associated_to_subcategory`),
  KEY `is_not_associated_to_subsubcategory` (`is_not_associated_to_subsubcategory`),
  KEY `product_list_id` (`product_list_id`),
  KEY `vendor_id` (`vendor_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1084370;

CREATE TABLE IF NOT EXISTS `tags` (
  `display_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `image_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `order` int(10) unsigned NOT NULL DEFAULT '0',
  `tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`tag_id`),
  KEY `type` (`type`),
  KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1084;

Answer 1

I would suggest attempting a query that doesn't use JOINs, simply for the fact that you don't use the JOINs for anything other than getting a count.

Try the following:

SELECT
t.name,
t.tag_id,
(
    SELECT COUNT(*) FROM product_tags pt
    INNER JOIN product_subcategories ps
    ON ps.product_id = pt.product_id
    INNER JOIN product p
    ON p.product_id = pt.product_id
    WHERE pt.tag_id = t.tag_id
    AND p.dont_display = 0
    AND ps.subsubcategory_id = 55
) AS qty_products
FROM tags t
WHERE 
t.type = 'brand'
AND EXISTS (
    SELECT * FROM product_tags pt
    INNER JOIN product_subcategories ps
    ON ps.product_id = pt.product_id
    INNER JOIN product p
    ON p.product_id = pt.product_id
    WHERE pt.tag_id = t.tag_id
    AND p.dont_display = 0
    AND ps.subsubcategory_id = 55
)
ORDER BY t.order,t.name

In this way, you only query the tags table, getting the results back in order initially. Then for each record you check if any are in subsubcategory 55, and otherwise skip that tag.

This should improve your query greatly, unless there are an enormous number of tags (and even then it still might improve things.)

Another improvement you can make is the one Kickstart suggested in the comments: add a covering index to the tags table:

ALTER TABLE tags
ADD INDEX `type_order_name` (`type`,`order`,`name`)

If you aren't familiar with multipart keys, just know that internally they are effectively stored as a concatenation of each of the columns, in the order the columns are listed in the key definition.

Therefore as long as you provide the type in the WHERE clause, the tags will be stored ordered by order and name, just as this query wants them. This will result in very fast sorting (because they will already be sorted in the index).