# Filesort on order by...

**URL:** https://forums.percona.com/t/filesort-on-order-by/480
**Category:** Other MySQL® Questions
**Created:** [October 7, 2007, 8:10am UTC](https://forums.percona.com/t/filesort-on-order-by/480 "2007-10-07T08:10:21Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![andrew.johnstone](https://avatars.discourse-cdn.com/v4/letter/a/df705f/32.png) [@andrew.johnstone](https://forums.percona.com/u/andrew.johnstone)
#### Post date: [October 7, 2007, 8:10am UTC](https://forums.percona.com/t/filesort-on-order-by/480/1 "2007-10-07T08:10:21Z")

</div>

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

---

<div class="post-metadata">

### Author: ![fmpwizard](https://avatars.discourse-cdn.com/v4/letter/f/e47774/32.png) [@fmpwizard](https://forums.percona.com/u/fmpwizard)
#### Post date: [October 22, 2007, 10:43pm UTC](https://forums.percona.com/t/filesort-on-order-by/480/2 "2007-10-22T22:43:18Z")

</div>

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

---

<div class="post-metadata">

### Author: ![fmpwizard](https://avatars.discourse-cdn.com/v4/letter/f/e47774/32.png) [@fmpwizard](https://forums.percona.com/u/fmpwizard)
#### Post date: [October 22, 2007, 11:00pm UTC](https://forums.percona.com/t/filesort-on-order-by/480/3 "2007-10-22T23:00:06Z")

</div>

… 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?