MySQL Forums
Forum List  »  Optimizer & Parser

How to avoid temporary and filesort?
Posted by: Jim Daemon
Date: June 13, 2006 08:49AM

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.

Options: Reply• Quote


Subject
Views
Written By
Posted
How to avoid temporary and filesort?
6368
June 13, 2006 08:49AM


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.