•  

Announcement

Announcement Module
Collapse
No announcement yet.

Filesort on order by...

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Filesort on order by...

    #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','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
    Tags: None

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

    Comment


    • #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?

      Comment

      Working...
      X