title: Make MySQL to choose the best index for a query
body: <p>In MySQL 5.6 DB I have a huge SQL table of the following structure:</p>
  
  <pre><code>CREATE TABLE `tbl_requests` (
      `request_id` BIGINT(20) UNSIGNED NOT NULL,
      `option_id` BIGINT(20) UNSIGNED NOT NULL,
      `symbol` VARCHAR(30) NOT NULL,
      `request_time` DATETIME(6) NOT NULL,
      `request_type` SMALLINT(6) NOT NULL,
      `count` INT(11) NOT NULL,
      PRIMARY KEY (`request_id`),
      INDEX `key_request_type_symbol` (`request_type`, `symbol`),
      INDEX `key_request_time` (`request_time`),
      INDEX `key_request_symbol` (`symbol`)
  );
  </code></pre>
  
  <p>There are over 800 million records in the table with about 25,000 varieties of <code>symbol</code> field and about 100 distinct values in <code>request_type</code>. My goal is to make as quick as possible a query like:</p>
  
  <pre><code>SELECT tbl_requests.*
  FROM tbl_requests  use index (key_request_type_symbol)
  -- use index (key_request_time) -- use index (key_request_type_symbol)
  WHERE (tbl_requests.request_time &gt;= '2016-02-23' AND 
         tbl_requests.request_time &lt;= '2016-12-23') 
  AND (tbl_requests.request_type IN (0, 1, 9))  
  [AND (tbl_requests.symbol = 'AAPL' ... )]
  ORDER BY tbl_requests.request_time DESC, tbl_requests.request_id DESC
  LIMIT 0,100;
  </code></pre>
  
  <p>with different varieties of filtering by <code>tbl_requests.symbol</code> field from no filter to a set of values to set of matching patterns to a mix and match.
  <br><br>
  What I see is that different indexes give the best performance in different cases, and MySQL is unable to guess which one will be better. For example, with no filter the fastest is <code>key_request_time</code> index (0.016 sec.) and MySQL properly selects it (result of EXPLAIN command):</p>
  
  <blockquote>
  <pre><code>"id": 1,
  "select_type": "SIMPLE",
  "table": "tbl_requests",
  "type": "range",
  "possible_keys": "key_request_type_symbol,key_request_time",
  "key": "key_request_time",
  "key_len": "8",
  "ref": null,
  "rows": 428944675,
  "Extra": "Using index condition; Using where"
  </code></pre>
  </blockquote>
  
  <p>If index <code>key_request_type_symbol</code> index was used this query would take just huge amount of time (maybe hours?). </p>
  
  <p>I use syntax</p>
  
  <pre><code>FROM tbl_requests use index (key_request_type_symbol)
  </code></pre>
  
  <p>to force using an index.</p>
  
  <p>When one symbol is used in the filter</p>
  
  <pre><code>AND (tbl_requests.symbol = 'BAC')
  </code></pre>
  
  <p>MySQL server is choosing the same <code>key_request_time</code> index, and the query takes more than 10 sec. But if <code>key_request_type_symbol</code> index is used, the query takes about 0.7 sec. Also, when using the first index, if the query is repeated again it keeps taking over 10 sec., while when using the second index, the repeated queries take 0.1 sec. <br>
  EXPLAIN info for <code>key_request_type_symbol</code> index:</p>
  
  <blockquote>
  <pre><code>"id": 1,
  "select_type": "SIMPLE",
  "table": "tbl_requests",
  "type": "range",
  "possible_keys": "key_request_type_symbol",
  "key": "key_request_type_symbol",
  "key_len": "34",
  "ref": null,
  "rows": 17117,
  "Extra": "Using index condition; Using where; Using filesort"
  </code></pre>
  </blockquote>
  
  <p>A lot less rows, but with filesort.</p>
  
  <p>It looks like in the case of <code>key_request_type_symbol</code> it matters how many matching rows are in the table. For "AMZN" symbol, rows = 79762, and time is 0.15 sec, while if using <code>key_request_time</code> index it takes 4.4 sec. But MySQL prefers it over <code>key_request_type_symbol</code>.</p>
  
  <p>It is clear to see in the following example. If I use:</p>
  
  <pre><code>tbl_requests.symbol LIKE 'A%' 
  </code></pre>
  
  <p>with <code>key_request_time</code> index it takes 0.172 sec.<br>
  with <code>key_request_type_symbol</code> index it takes 173 sec. (~1000 times slower)<br>
  rows=6367732</p>
  
  <p>For:</p>
  
  <pre><code>tbl_requests.symbol LIKE 'AM%' 
  </code></pre>
  
  <p>with <code>key_request_time</code> index it takes 0.640 sec.<br>
  with <code>key_request_type_symbol</code> index it takes 2.2 sec. (~3 times slower)<br>
  rows=838822</p>
  
  <p>For:</p>
  
  <pre><code>tbl_requests.symbol LIKE 'AMZ%' 
  </code></pre>
  
  <p>with <code>key_request_time</code> index it takes 4.5 sec.<br>
  with <code>key_request_type_symbol</code> index it takes 0.15 sec.  (~30 times faster)<br>
  rows=73083</p>
  
  <p>For:</p>
  
  <pre><code>tbl_requests.symbol LIKE 'AMZN%' 
  </code></pre>
  
  <p>with <code>key_request_time</code> index it takes 4.4 sec.<br>
  with <code>key_request_type_symbol</code> index it takes 0.15 sec.  (~30 times faster)<br>
  rows=79762</p>
  
  <p>Also when using <code>key_request_type_symbol</code> index the execution gets much faster when the same symbol filter is used again while for <code>key_request_time</code> timing stays about the same.</p>
  
  <p>I am going to receive a lot of queries with one symbol so I need them to be fast. But also I may receive queries filtered by many symbols. How can I force the server to pick the fastest way for me in each case?</p>
  
  <p>One method I can imagine is to send EXPLAIN statement ahead and check the number of expected rows in case of <code>key_request_type_symbol</code> index, and then modify the query to use this or that index accordingly (like, if rows is over 300000, use <code>key_request_time</code>).</p>
  
  <p>But maybe I am missing something? Maybe the indexes are not correct (but I couldn't find better)? It would be nice to keep the query unmodified and force MySQL to be smart enough to choose the fastest way automatically.</p>