How to change my MySQL query or table layout to use index

Question

I'm searching for an effective index for my table layout. Or maybe for hints to change my table layout.

I have a table with start, end and actual values (timestamps, simplified with low numbers in the examples below). actual can increase until it reaches end.

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);

In my SELECT result I want all rows from the table with actual values smaller the current timestamp (to simplify the example lets say the current timestamp is 7). Additionally I want only these rows with actual values smaller than end. This second condition makes the problem.

SELECT `id`
  FROM `t1`
  WHERE `actual` < `end`
    AND `actual` < 7;

+----+
| id |
+----+
|  1 |
|  4 |
+----+
2 rows in set (0.00 sec)

The index will be used for actual < 7, but I suppose not for actual < end. Because the comparision of actual < end will be done for all ancient rows the query become slower with every new (old) row in the table.

end < 7 does not solve the problem, because I want outdated rows with actual < end in the result.

I could add a new computed column to the table named remaining with a value of end - actual and use the WHERE condition WHERE remaining > 0 AND actual < 7 (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 end and forget to also update the computed remainig I have a broken row.

Explain result:

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | actual        | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

Change the key definition to:

KEY `actual_end` (`actual`,`end`)

Explain result:

+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| 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 |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+

This last Explain prove that the index is used for actual < 7 and not for actual < end. With 1 billion ancient rows the last condition will examine 1 billion rows. I want to optimize this problem.

You asume the index is not used. What does `explain` tells you about your query? For your remaining-col. I also wouldn`t do it as you can calculate it. But if you want to make sure the col is update you could use a trigger for that. So you make save that the col is updated every time the `end` col is updated. — bish, Sep 05 '13 at 08:11

score 0 · Answer 1 · edited May 23 '17 at 12:28

0

You can create concatenated (composite) index on both columns actual and end, so index supports both conditions.

ALTER TABLE t1 ADD INDEX `ActualEnd` (`actual`, `end`)

You can also check this thread..

UPDATE:

Explain you have just added shows that your query is Using index, after adding composite index which is optimal for this query. There is no need for temp tables or writing to disc. Since your table is using InnoDB engine, and it has PRIMARY key on id column, PRIMARY key is automatically added to the end of your composite index making it posibble to satisfy all you ask from a query directly from index. You are not performing any operations on indexed values, not using any functions that would break the use of index, so there is no reason why index shouldn't be used. Explain just proves it.

edited May 23 '17 at 12:28

Community

1
1

answered Sep 05 '13 at 08:11

Zagor23

1,953
12
14

Yes I know composite index. But are you shure that it would help in this situation. I compare two columns of the same row, This is maybe a calculation for which no index can be used. – Frank Olschewski Sep 05 '13 at 08:48
What does the `rows` column in the Explain result mean. Doesn't the value 3 means that the index is used but there are 3 rows selected with using the index. These 3 rows are from the condition `actual < 7` not `actual < end`. I want an index that match additionally the condition `actual < end`. – Frank Olschewski Sep 05 '13 at 09:08
`rows` column, as it says in manual is `Estimate of rows to be examined` which means that query optimizer would EXAMINE 3 rows in this case, but with second condition it removes one of them. – Zagor23 Sep 05 '13 at 09:32
Yes, and if I have 1 billion ancient rows with `actual < 7` does he examine 1 billion rows? – Frank Olschewski Sep 05 '13 at 09:36
Yes, because they all satisfy the condition... The second condition (`actual < end`) is basically forcing the check on every row in table to see if `actual < end`, so the only thing query optimizer can do is to try to narrow the initial set by getting only those rows that have `actual < 7`, and then filter out those that have `actual >= end`. – Zagor23 Sep 05 '13 at 09:50
And this is exactly for what I search a solution! Unfortunately your answer only clarify the problem and don't provide a solution. – Frank Olschewski Sep 05 '13 at 09:54

How to change my MySQL query or table layout to use index

1 Answers1