How to avoid temporary and filesort?
I have the following query:
SELECT DISTINCT p.products_id, pd.products_name
FROM products p, products_description pd, products_to_categories p2c, categories c
WHERE p.products_status = '1'
AND p.products_ordered >0
AND p.products_id = pd.products_id
AND pd.language_id = '1'
AND p.products_id = p2c.products_id
AND p2c.categories_id = c.categories_id
AND '222'
IN (
c.categories_id, c.parent_id
)
ORDER BY p.products_ordered DESC , pd.products_name
LIMIT 5
Which according to EXPLAIN is doing the following:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p2c index PRIMARY PRIMARY 8 NULL 1692 Using index; Using temporary; Using filesort
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 xr.p2c.products_id 1 Using where
1 SIMPLE pd eq_ref PRIMARY PRIMARY 8 xr.p.products_id,const 1
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 xr.p2c.categories_id 1 Using where; Distinct
How can I keep this query from either creating a temporary file, using filesort, or both?
Following are the table definitions:
CREATE TABLE categories (
categories_id int(11) NOT NULL auto_increment,
categories_image varchar(64) default NULL,
categories_thumbnail_image varchar(64) default NULL,
parent_id int(11) NOT NULL default '0',
sort_order int(3) default NULL,
date_added datetime default NULL,
last_modified datetime default NULL,
hidden int(3) NOT NULL default '0',
PRIMARY KEY (categories_id),
KEY idx_categories_parent_id (parent_id),
KEY sort_order (sort_order)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE products (
products_id int(11) NOT NULL auto_increment,
products_quantity int(4) NOT NULL default '0',
products_model varchar(12) default NULL,
products_image varchar(64) default NULL,
products_image_thumbnail varchar(64) default NULL,
products_subimage1 varchar(64) default NULL,
products_subimage2 varchar(64) default NULL,
products_subimage3 varchar(64) default NULL,
products_subimage4 varchar(64) default NULL,
products_subimage5 varchar(64) default NULL,
products_subimage6 varchar(64) default NULL,
products_subimage1th varchar(64) default NULL,
products_subimage2th varchar(64) default NULL,
products_subimage3th varchar(64) default NULL,
products_subimage4th varchar(64) default NULL,
products_subimage5th varchar(64) default NULL,
products_subimage6th varchar(64) default NULL,
products_price decimal(15,4) NOT NULL default '0.0000',
products_date_added datetime NOT NULL default '0000-00-00 00:00:00',
products_last_modified datetime default NULL,
products_date_available datetime default NULL,
products_weight decimal(5,2) NOT NULL default '0.00',
products_status tinyint(1) NOT NULL default '0',
products_tax_class_id int(11) NOT NULL default '0',
manufacturers_id int(11) default NULL,
products_ordered int(11) NOT NULL default '0',
maxorder int(4) default NULL,
PRIMARY KEY (products_id),
KEY idx_products_date_added (products_date_added),
KEY products_model (products_model)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE products_description (
products_id int(11) NOT NULL auto_increment,
language_id int(11) NOT NULL default '1',
products_name varchar(64) NOT NULL default '',
products_description text,
products_url varchar(255) default NULL,
products_viewed int(5) default '0',
products_head_title_tag varchar(80) default NULL,
products_head_desc_tag longtext,
products_head_keywords_tag longtext,
products_subhead text,
products_subfoot text,
products_vod_video_id varchar(255) default NULL,
products_vod_type tinyint(4) NOT NULL default '0',
PRIMARY KEY (products_id,language_id),
KEY products_name (products_name)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE products_to_categories (
products_id int(11) NOT NULL default '0',
categories_id int(11) NOT NULL default '0',
PRIMARY KEY (products_id,categories_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Great spirits have always found violent opposition from mediocre minds. The latter cannot understand it when a person does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses their intelligence.