title: How to change my MySQL query or table layout to use index
body: <p>I'm searching for an effective index for my table layout. Or maybe for hints to change my table layout.</p>
  
  <p>I have a table with <code>start</code>, <code>end</code> and <code>actual</code> values (timestamps, simplified with low numbers in the examples below). <code>actual</code> can increase until it reaches <code>end</code>. </p>
  
  <pre><code>CREATE TABLE `t1` (
    `id` int(10) unsigned NOT NULL DEFAULT '0',
    `start` int(10) unsigned NOT NULL DEFAULT '0',
    `actual` int(10) unsigned NOT NULL DEFAULT '0',
    `end` int(10) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `actual` (`actual`)
  ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  
  INSERT INTO `t1`
  (`id`, `start`, `actual`, `end`)
  VALUES 
  (1, 1, 0, 5),
  (2, 1, 6, 6),
  (3, 2, 8, 9),
  (4, 2, 5, 9);
  </code></pre>
  
  <p>In my <code>SELECT</code> result I want all rows from the table with <code>actual</code> values smaller the current timestamp (to simplify the example lets say the current timestamp is 7). Additionally I want only these rows with <code>actual</code> values smaller than <code>end</code>. This second condition makes the problem.</p>
  
  <pre><code>SELECT `id`
    FROM `t1`
    WHERE `actual` &lt; `end`
      AND `actual` &lt; 7;
  
  +----+
  | id |
  +----+
  |  1 |
  |  4 |
  +----+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>The index will be used for <code>actual &lt; 7</code>, but I suppose not for <code>actual &lt; end</code>. Because the comparision of <code>actual &lt; end</code> will be done for all ancient rows the query become slower with every new (old) row in the table.</p>
  
  <p><code>end &lt; 7</code> does not solve the problem, because I want outdated rows with <code>actual &lt; end</code> in the result.</p>
  
  <p>I could add a new computed column to the table named <code>remaining</code> with a value of <code>end</code> - <code>actual</code> and use the <code>WHERE</code> condition <code>WHERE remaining &gt; 0 AND actual &lt; 7</code> (and sure change the index or create a new one). But I have a problem with this, it feels like a bad table layout. If someone updates <code>end</code> and forget to also update the computed <code>remainig</code> I have a broken row.</p>
  
  <p>Explain result:</p>
  
  <pre><code>+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
  +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  |  1 | SIMPLE      | t1    | ALL  | actual        | NULL | NULL    | NULL |    4 | Using where |
  +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  </code></pre>
  
  <p>Change the key definition to:</p>
  
  <pre><code>KEY `actual_end` (`actual`,`end`)
  </code></pre>
  
  <p>Explain result:</p>
  
  <pre><code>+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
  | id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |
  +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
  |  1 | SIMPLE      | t1    | range | actual_end    | actual_end | 4       | NULL |    3 | Using where; Using index |
  +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
  </code></pre>
  
  <p>This last Explain prove that the index is used for <code>actual &lt; 7</code> and <em>not</em> for <code>actual &lt; end</code>. With 1 billion ancient rows the last condition will examine 1 billion rows. I want to optimize this problem.</p>