How to build index and query with time range and id sort?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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:

CodePudding user response：

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!

CodePudding user response：

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