MySQL Forums
Forum List  »  Optimizer & Parser

Why does this query filesort?
Posted by: Jim Daemon
Date: July 27, 2006 12:05PM

I have the following table definition:

CREATE TABLE `currencies` (
`currencies_id` int(11) NOT NULL auto_increment,
`title` varchar(32) NOT NULL default '',
`code` char(3) NOT NULL default '',
`symbol_left` varchar(12) default NULL,
`symbol_right` varchar(12) default NULL,
`decimal_point` char(1) default NULL,
`thousands_point` char(1) default NULL,
`decimal_places` char(1) default NULL,
`value` float(13,8) default NULL,
`last_updated` datetime default NULL,
PRIMARY KEY (`currencies_id`),
UNIQUE KEY `code` (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

The following query currently takes an average of 0.869 seconds to run.
select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies;

The results of EXPLAIN for the above query are:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE currencies ALL NULL NULL NULL NULL 4


I have tried modifying the query for the following but they have had no change:
order by currencies_id
order by code (after creating a unique index on the code field)

How can I prevent this query from doing a filesort for 4 records and taking so long to execute?

Options: ReplyQuote


Subject
Views
Written By
Posted
Why does this query filesort?
2734
July 27, 2006 12:05PM
1816
August 01, 2006 11:14PM


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.