title: MySQL Query Slows Dramatically With Order BY included
body: <p>I know this has been addressed, but the solutions have not helped me.</p>
  
  <p>I have a table with 3 million records. The columns are indexed.</p>
  
  <pre><code>SELECT * FROM checklist_search WHERE player LIKE '%Mike%' AND player LIKE '%Trout%'
  ORDER BY 0+year ASC, search_data ASC, 0+cardnumber ASC  
  LIMIT 0, 50
  </code></pre>
  
  <p>Without the ORDER BY the query is fast - Query took 0.0156 sec
  With the ORDER BY Query took 3.7068 sec</p>
  
  <p>Based on other answers to this problem I tried</p>
  
  <pre><code>SELECT * FROM(SELECT * FROM checklist_search WHERE player LIKE '%Mike%' AND player LIKE '%Trout%'
    ) as myalias ORDER BY 0+year ASC, search_data ASC, 0+cardnumber ASC
  LIMIT 0, 50
  </code></pre>
  
  <p>But the results are the same.</p>
  
  <p>Here is the table and index structure</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `checklist_search` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `page_type` varchar(10) NOT NULL DEFAULT '',
    `checklist_db` varchar(20) NOT NULL DEFAULT '',
    `set_id` int(10) unsigned NOT NULL,
    `card_id` int(11) NOT NULL,
    `search_data` varchar(255) NOT NULL DEFAULT '0',
    `year` varchar(10) NOT NULL DEFAULT '0',
    `brand` varchar(100) NOT NULL,
    `issue` varchar(100) NOT NULL,
    `set_name` varchar(150) NOT NULL DEFAULT '',
    `cardnumber` varchar(10) NOT NULL,
    `player` varchar(150) NOT NULL DEFAULT '',
    `team` varchar(150) NOT NULL DEFAULT '',
    `rc` int(1) NOT NULL,
    `sp` int(1) NOT NULL,
    `gu` int(1) NOT NULL,
    `auto` int(1) NOT NULL,
    `parallel` int(1) NOT NULL,
    `insert_card` int(1) NOT NULL,
    `card_count` int(10) NOT NULL,
    `image_count` int(10) NOT NULL,
    `print_run` varchar(20) NOT NULL,
    `display_image_front` varchar(120) NOT NULL,
    `display_image_back` varchar(120) NOT NULL,
    `seo_url` varchar(255) NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `card_id` (`card_id`),
    KEY `checklist_db` (`checklist_db`),
    KEY `year` (`year`),
    KEY `player` (`player`),
    KEY `team` (`team`),
    KEY `print_run` (`print_run`),
    KEY `rc` (`rc`),
    KEY `set_id` (`set_id`),
    KEY `search_data` (`search_data`),
    KEY `cardnumber` (`cardnumber`),
    FULLTEXT KEY `player_2` (`player`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=3203103 ;
  </code></pre>
  
  <p>Any ideas would be greatly appreciated.</p>
  
  <p>Thanks
  Mike</p>
  
  <pre><code>EDIT: Here is the result for 
  EXPLAIN SELECT *
  FROM checklist_search
  WHERE player LIKE '%Mike%'
  AND player LIKE '%Trout%'
  ORDER BY year ASC , search_data ASC , cardnumber ASC
  LIMIT 0 , 50
  
  id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
  1   SIMPLE  checklist_search    ALL     NULL    NULL    NULL    NULL    2837591     Using where; Using filesort
  </code></pre>