title: Improve speed execution of MySQL select query
body: <p>I have 2 MySQL tables with several millions of rows and I'm trying to perform a query selection for get specific data column from both tables. Despite my first good expectations the execution of the query selection takes several seconds (around 5 seconds) also with an index applied on the WHERE condition.</p>
  
  <h3>Create statement for the 2 MySQL tables:</h3>
  
  <pre><code>CREATE TABLE `T1` (
    `T1_id` int(15) NOT NULL AUTO_INCREMENT, 
    `T1_val1` varchar(45) NOT NULL,
    `T1_val2` varchar(45) NOT NULL,
    `T1_val3` bigint(11) NOT NULL,
    `T1_val4` datetime NOT NULL, 
    `T1_val5` varchar(100) NOT NULL, 
    `T1_val6` float NOT NULL,
    `T1_val7` datetime NOT NULL,
    `T1_val8` varchar(100) NOT NULL,
    `T1_val9` varchar(100) NOT NULL, 
    `T1_val10` varchar(100) NOT NULL,
    PRIMARY KEY (`T1_id`),
    KEY `T1_val4` (`T1_val4`)
  ) ENGINE=InnoDB AUTO_INCREMENT=53885653 DEFAULT CHARSET=latin1;
  
  CREATE TABLE `T2` (
    `T2_id` int(11) NOT NULL,
    `T2_val1` float NOT NULL,
    `T2_val2` float NOT NULL,
    `T2_val3` varchar(45) NOT NULL,
    PRIMARY KEY (`T2_id`),
    KEY `T2_val3` (`T2_val3`),
    KEY `T2_val1_2` (`T2_val1`,`T2_val2`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>As you can see both tables have an <strong>AI primary key</strong> and <strong>foreign key</strong> that matches as <code>one-to-one relationship</code> between them (<code>T1_id</code> and <code>T2_id</code>). And we have an <strong>index</strong> applied on <code>T1</code> for <code>T1_val4</code> that is in a <strong>datetime format</strong>.</p>
  
  <h3>The selection query</h3>
  
  <pre><code>SELECT 
        T1_val5, 
        T2_val1, 
        T2_val2, 
        T2_val3, 
        T1_val9, 
        count(T2_val1) as cnt, 
        T1_val4
     FROM 
        T1 USE INDEX (T1_val4)
           INNER JOIN T2 
              ON T1.T1_id = T2.T2_id 
     WHERE 
        T1_val4 BETWEEN '2016-02-18 15:00:00' 
                    AND '2016-02-18 16:59:59'
     GROUP BY 
        T2_val1, 
        T2_val2, 
        T2_val3, 
        T1_val9, 
        T1_val5
     order by 
        T1_val4 ASC;
  </code></pre>
  
  <p>As you can notice, I have specified a HINT for the index in order to tell to MySQL to use that specific index for the datetime column. Actually if I extend the datetime range in the WHERE condition to several hours well.. for example <code>BETWEEN '2016-02-18 15:00:00' AND '2016-02-18 23:59:59'</code> the execution time grows up till 50/100 seconds. Probably I'm missing something in the logic.</p>
  
  <h3>The EXPLAIN output</h3>
  
  <pre><code>+-------+---------------+-----------+-----------+-------------------+-----------+---------------+-----------+-----------+------------------------------------------------------------+
  |   ID  |   SELECT_TYPE |   TABLE   |   TYPE    |   POSSIBLE_KEYS   |   KEY     |   KEY_LEN     |   REF     |   ROWS    |       EXTRA                                                |
  +-------+---------------+-----------+-----------+-------------------+-----------+---------------+-----------+-----------+------------------------------------------------------------+
  |   1   |   SIMPLE      |   T1      |   range   |   T1_val4         |   T1_val4 |      5        |   NULL    |   10670   |   "Using index condition; Using temporary; Using filesort" |
  +-------+---------------+-----------+-----------+-------------------+-----------+---------------+-----------+-----------+------------------------------------------------------------+
  |   1   |   SIMPLE      |   T2      |   eq_ref  |   PRIMARY         |   PRIMARY |      4        |   T1_id   |   1       |     NULL                                                   |
  +-------+---------------+-----------+-----------+-------------------+-----------+---------------+-----------+-----------+------------------------------------------------------------+
  </code></pre>
  
  <h3>The EXPLAIN output updated with compound index</h3>
  
  <p><strong>(As suggested from @O. Jones)</strong></p>
  
  <pre><code>+-------+---------------+-----------+-----------+------------------------------+-----------+---------------+-----------+-----------+---------------------------------------------------------------+
  |   ID  |   SELECT_TYPE |   TABLE   |   TYPE    |   POSSIBLE_KEYS              |   KEY     |   KEY_LEN     |   REF     |   ROWS    |       EXTRA                                                   |
  +-------+---------------+-----------+-----------+------------------------------+-----------+---------------+-----------+-----------+---------------------------------------------------------------+
  |   1   |   SIMPLE      |   T1      |   range   |   "PRIMARY,ix_rlf"           |   ix_rlf  |      5        |   NULL    |   10906   |   "Using where; Using index; Using temporary; Using filesort" |
  +-------+---------------+-----------+-----------+------------------------------+-----------+---------------+-----------+-----------+---------------------------------------------------------------+
  |   1   |   SIMPLE      |   T2      |   eq_ref  |   "PRIMARY,ix_cc"            |   PRIMARY |      4        |   T1_id   |   1       |     NULL                                                      |
  +-------+---------------+-----------+-----------+------------------------------+-----------+---------------+-----------+-----------+---------------------------------------------------------------+
  </code></pre>
  
  <p><strong>ix_rlf</strong> is the compound index of <code>T1_val4</code>, <code>T1_val9</code>, <code>T1_val5</code>and <strong>ix_cc</strong> is the compound index suggested from @Tom Shir for T2 made of <code>T2_id</code>, <code>T2_val1</code>, <code>T2_val2</code>, <code>T2_val3</code>.</p>
  
  <h3>Query execution plan visual schema</h3>
  
  <p><strong>(considering 2 HOURS as interval, and in this case the result of the query it's around 6632 rows and 6/7 seconds as execution time)</strong></p>
  
  <p><a href="https://i.stack.imgur.com/hyg0Z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hyg0Z.png" alt="query execution plan schema"></a></p>