Hi,
Could someone please help me to get rid of "Using temporary; Using filesort"
from the following SQL statement?
SELECT a.document_id AS id, attr_name AS name, attr_value AS value, attr_order
AS ord FROM attributes a INNER JOIN status s ON (a.document_id = s.document_id)
WHERE update_flag = 2 order by a.document_id,attr_name,attr_order;
attributes table has 17,416,181 records and status table has 335,268 records.
For each document_id in status table, there will be many records in attributes
table.
The above query took around 762 seconds in the worst case.
For each excution there will be upto 5000 records returned from status table
with update_flag = 2 and around 300,000 records from attributes table.
# Query_time: 762 Lock_time: 0 Rows_sent: 262293 Rows_examined: 791879SELECT
a.document_id AS id, attr_name AS name, attr_value AS value, attr_order AS ord
FROM attributes a INNER JOIN status s ON (a.document_id = s.document_id) WHERE
update_flag = 2 order by a.document_id,attr_name,attr_order;
mysql> explain SELECT a.document_id AS id, attr_name AS name, attr_value AS
value, attr_order AS ord FROM attributes a INNER JOIN status s ON
(a.document_id = s.document_id) WHERE update_flag = 2 order by
a.document_id,attr_name,attr_order \G*************************** 1. row
*************************** id: 1 select_type: SIMPLE table:
s type: refpossible_keys: ix_status_documentid,ix_status_updateflag
key: ix_status_updateflag key_len: 2 ref: const
rows: 1 Extra: Using where; Using temporary; Using
filesort*************************** 2. row ***************************
id: 1 select_type: SIMPLE table: a type: refpossible_keys:
index_three,ix_attributes_documentid key: index_three key_len:
257 ref: jacobjitems.s.document_id rows: 52 Extra:2
rows in set (0.00 sec)
mysql> show create table attributes \G*************************** 1. row
*************************** Table: attributesCreate Table: CREATE TABLE
`attributes` ( `document_id` varchar(255) NOT NULL, `attr_name` varchar(64)
NOT NULL, `attr_value` varchar(4000) NOT NULL, `attr_order` smallint(6)
default NULL, `attr_include` tinyint(1) default '1', KEY `index_three`
(`document_id`,`attr_name`,`attr_order`), KEY `ix_attributes_documentid`
(`document_id`), KEY `ix_attributes_attr_name` (`attr_name`)) ENGINE=MyISAM
DEFAULT CHARSET=latin1
mysql> show create table status \G*************************** 1. row
*************************** Table: statusCreate Table: CREATE TABLE
`status` ( `document_id` varchar(255) NOT NULL, `update_flag` tinyint(4)
default '1', `collection_name` varchar(128) NOT NULL, KEY
`ix_status_documentid` (`document_id`), KEY `ix_status_updateflag`
(`update_flag`)) ENGINE=MyISAM DEFAULT CHARSET=latin11 row in set (0.00 sec)
Thanks in advance for your help !!!
-Jeesmon
