How to get rid of 'filesort'?

[Ruslan Osmanov]

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.