Question 112856 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to build index and query with time range and id sort?

Here is the table data

id time amount
1 20221104 15
2 20221104 10
3 20221105 7
4 20221105 19
5 20221106 10

The id and time field is asc, but time can be same.

The rows are very large, so we don’t want to use page limit offset method, but with cursor id.

first query:

select * from t where time > xxx and time < yyy order by id asc limit 10;

get the biggest id zzz, then

next query:

select * from t where time > xxx and time < yyy and id > zzz order by id asc limit 10;

How should I build the index?
If I use id as index, the time range will cause huge scan if time is far away.
And If I use time as index, seek id will not be effective.

Tags:

4

Answers


  1. 0

    The following index should be enough for both queries:

    alter table t add index `time_id` (`time`,`id`);

    Note, use proper date/datetime data types , will save a lot of pain in the future

    Login or Signup to reply.
  2. 0

    The key is composite index by leftmost prefixing principle. But both queries here start with
    range expression. So I suppose that simply creating index on (a, b) is unable to optimize effectively
    because indexing process stops after range condition. It is enough to create index like this:

    CREATE INDEX index_time ON t (`time`)

    More can be referenced here:

    Login or Signup to reply.
  3. 0

    First I agree with @ErgestBasha Suggestion:

    If you follow the general performance rules:

    CREATE TABLE t (id INT, time DATE, amount DEC(3,1));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t VALUES
    -> (1, '2022-11-04', 15),
    -> (2, '2022-11-04', 10),
    -> (3, '2022-11-05', 7),
    -> (4, '2022-11-05', 19),
    -> (5, '2022-11-06', 10);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+------+------------+--------+
| id   | time       | amount |
+------+------------+--------+
|    1 | 2022-11-04 |   15.0 |
|    2 | 2022-11-04 |   10.0 |
|    3 | 2022-11-05 |    7.0 |
|    4 | 2022-11-05 |   19.0 |
|    5 | 2022-11-06 |   10.0 |
+------+------------+--------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE t ADD INDEX idx_time_id (time,id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM t;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t     |          1 | idx_time_id |            1 | time        | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t     |          1 | idx_time_id |            2 | id          | A         |           5 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM t WHERE time > '2022-11-04' AND time < '2022-11-06' AND id > 3 ORDER BY id ASC LIMIT 10;
+------+------------+--------+
| id   | time       | amount |
+------+------------+--------+
|    4 | 2022-11-05 |   19.0 |
+------+------------+--------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t WHERE time > '2022-11-04' AND time < '2022-11-06' AND id > 3 ORDER BY id ASC LIMIT 10;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_time_id   | idx_time_id | 4       | NULL |    2 |    33.33 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

    As you can see, It uses the indexes defined (time,id) and uses the range scan access method. Also Extra column you can see that index is used during operation!

    Login or Signup to reply.
  4. 0

    See this for iterating through a compound key:
    http://mysql.rjweb.org/doc.php/deletebig#iterating_through_a_compound_key
    It cannot be done with two ANDs; tt needs one AND and one OR.

    See this for why OFFSET should be avoided when Paginating

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search