MySQL Forums
Forum List  »  Optimizer & Parser

How to get rid of 'filesort'?
Posted by: Ruslan Osmanov
Date: February 28, 2010 08:51AM

Hi all. Cannot get EXPLAIN without 'filesort' using group by and order by.
There are tables of shop products, folders and folder items(with product and folder IDs):
CREATE TABLE `s3_shop_product` (
  `product_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` mediumint(7) unsigned NOT NULL DEFAULT '0',
  `product_article` varchar(100) NOT NULL DEFAULT '',
  `product_name` varchar(200) NOT NULL DEFAULT 'noname',
  `product_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `product_amount` mediumint(7) unsigned NOT NULL DEFAULT '0',
  `product_note` text,
  `product_measure` varchar(20) NOT NULL DEFAULT '',
  `product_body` text,
  `image_id` int(10) unsigned NOT NULL DEFAULT '0',
  `status` tinyint(1) unsigned DEFAULT '1',
  `1c_id` varchar(20) DEFAULT '',
  `vendor_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `new` tinyint(1) unsigned DEFAULT '2',
  `aliases` text,
  `special` tinyint(1) unsigned DEFAULT '2',
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`product_id`),
  KEY `shop_id` (`shop_id`),
  KEY `u` (`shop_id`,`product_id`),
  KEY `1c` (`shop_id`,`1c_id`(4)),
  KEY `name` (`shop_id`,`product_name`(4)),
  KEY `pname` (`product_name`(10)),
  KEY `vendor_id` (`vendor_id`),
  FULLTEXT KEY `product_article` (`product_article`,`product_name`,`product_note`),
  FULLTEXT KEY `product_note` (`product_note`,`product_article`,`product_name`),
  FULLTEXT KEY `product_note_2` (`product_note`,`product_name`,`product_article`)
) ENGINE=MyISAM AUTO_INCREMENT=100232 DEFAULT CHARSET=utf8;

CREATE TABLE `s3_folder` (
  `folder_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `folder_name` varchar(100) NOT NULL DEFAULT '',
  `tree_id` int(10) unsigned NOT NULL DEFAULT '0',
  `_left` smallint(4) unsigned NOT NULL DEFAULT '0',
  `_right` smallint(4) unsigned NOT NULL DEFAULT '0',
  `_level` smallint(4) unsigned NOT NULL DEFAULT '0',
  `system` tinyint(1) NOT NULL DEFAULT '0',
  `items` smallint(5) unsigned NOT NULL DEFAULT '0',
  `image_id` int(10) unsigned NOT NULL DEFAULT '0',
  `big_image_id` int(10) unsigned NOT NULL DEFAULT '0',
  `flash_id` int(10) unsigned NOT NULL DEFAULT '0',
  `folder_desc` text,
  PRIMARY KEY (`folder_id`),
  KEY `tree_id` (`tree_id`)
) ENGINE=MyISAM AUTO_INCREMENT=81878 DEFAULT CHARSET=utf8;

CREATE TABLE `s3_folder_item` (
  `folder_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL DEFAULT '0',
  `position` smallint(5) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`folder_id`,`item_id`),
  KEY `u` (`folder_id`,`position`)
) ENGINE=MyISAM AUTO_INCREMENT=81877 DEFAULT CHARSET=utf8;

The query:
explain  
select p.product_id /*....*/   
from s3_shop_product p 
join s3_folder_item as fi on fi.item_id=p.product_id  
join s3_folder f on fi.folder_id=f.folder_id  
where p.shop_id=6 and p.status=1 and f.tree_id=30 and f._left>=4 and f._right<=11 
group by p.product_id 
order by p.product_id  
limit 0, 5 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: f
         type: ref
possible_keys: PRIMARY,tree_id
          key: tree_id
      key_len: 4
          ref: const
         rows: 8
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: fi
         type: ref
possible_keys: PRIMARY,u
          key: PRIMARY
      key_len: 4
          ref: s3_small.f.folder_id
         rows: 1
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: eq_ref
possible_keys: PRIMARY,shop_id,u,1c,name
          key: PRIMARY
      key_len: 4
          ref: s3_small.fi.item_id
         rows: 1
        Extra: Using where
Tried different indexes and subqueries. One thing could replace the query:
1) get product IDs
2) implode IDs putting them into the next query with IN().
But the query becomes about 50K on the working server and occasionally appears in slow queries.

Regards.

Options: ReplyQuote


Subject
Views
Written By
Posted
How to get rid of 'filesort'?
3755
February 28, 2010 08:51AM
1808
March 01, 2010 04:51PM
1939
March 01, 2010 11:27PM
1609
March 02, 2010 04:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.