Filesort on order by...

MySQL & MariaDB Other MySQL® Questions
1

Hi,

How can I remove a filesort from the following query I simply want to order the results ascending by the primary key ftid…

Thanks

Andrew

SELECT * FROM trade_messages WHERE processed_cfd = ‘NO’ AND LEFT(symbol,3) IN (‘E:A’,‘E:B’,‘E:C’,‘E:D’,‘E:E’,‘E:F’,‘E:G’,‘E:H’,‘E:I’,‘E:J’,‘E:K’,‘E:L’,‘E:M’,‘E:N’,‘E:O’,‘E:P’,‘E:Q’,‘E:R’,‘E:S’,‘E:T’,‘E:U’,‘E:V’,‘E:W’,‘E:X’,‘E:Y’,‘E:Z’) ORDER BY ftid ASC LIMIT 400

CREATE TABLE trade_messages ( ftid bigint(20) unsigned NOT NULL auto_increment, symbol varchar(6) default NULL, trade_seq bigint(20) unsigned NOT NULL default ‘0’, trade_size int(10) unsigned default NULL, trade_price double default NULL, ask_price double default NULL, bid_price double default NULL, date_trade datetime default NULL, chg double default NULL, pct_chg double default NULL, trade_vol int(10) unsigned default NULL, trade_high double default NULL, trade_low double default NULL, trade_open double default NULL, trade_close double default NULL, processed enum(‘YES’,‘NO’) NOT NULL default ‘NO’, processed_cfd enum(‘YES’,‘NO’) NOT NULL default ‘NO’, syncTimeStamp datetime default NULL, lastUpdateTime datetime default NULL, startTime timestamp NULL default NULL, endTime timestamp NULL default NULL, processingTime float default NULL, PRIMARY KEY (ftid), KEY symbol (symbol), KEY processed (processed), KEY searchArgs (processed,trade_seq), KEY searchEpic (symbol,trade_seq), KEY searching (processed_cfd,trade_seq), KEY procPending (processed_cfd,symbol(3),ftid)) ENGINE=InnoDB DEFAULT CHARSET=latin1

2

Could you include the output of running EXPLAIN on your query?

3

… and do you need to select all columns in your query?

I’d also try this (which does not look very pretty but may help)

(SELECT * FROM trade_messages WHERE processed_cfd = ‘NO’ AND LEFT(symbol,3) like “E:A” ORDER BY ftid ASC LIMIT 400)UNION ALL(SELECT * FROM trade_messages WHERE processed_cfd = ‘NO’ AND LEFT(symbol,3) like “E:B” ORDER BY ftid ASC LIMIT 400)UNION ALL(SELECT * FROM trade_messages WHERE processed_cfd = ‘NO’ AND LEFT(symbol,3) like “E:C” ORDER BY ftid ASC LIMIT 400)UNION ALL…) ORDER BY ftid ASC LIMIT 400

I think that this will eliminate the filesort

(could you also include the output EXPLAIN on this query as well?