StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to change my MySQL query or table layout to use index
text
Body
copied!<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` < `end` AND `actual` < 7; +----+ | id | +----+ | 1 | | 4 | +----+ 2 rows in set (0.00 sec) </code></pre> <p>The index will be used for <code>actual < 7</code>, but I suppose not for <code>actual < end</code>. Because the comparision of <code>actual < 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 < 7</code> does not solve the problem, because I want outdated rows with <code>actual < 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 > 0 AND actual < 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 < 7</code> and <em>not</em> for <code>actual < end</code>. With 1 billion ancient rows the last condition will examine 1 billion rows. I want to optimize this problem.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload