So I have 2 tables, each of them is 25GB.
Table t1 has an index on a single column and t2 has a composite index.
All my queries are simple query making use of the index.
my t2 table
CREATE TABLE `t2` (
`uid` bigint(20) DEFAULT NULL,
`time` bigint(20) DEFAULT NULL,
`content` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
KEY `composite` (`uid`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
my query
explain select * from t2 where uid between 1 and 10000 and time between 11 and 10000000;
the explain result:
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | q3 | NULL | range | composite | composite | 18 | NULL | 2548 | 11.11 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.19 sec)
The strange thing that I observed is that when I restore a database from the snapshot, the t2 would become super slow with 3000 latency but the t1 would become normal(with 100 latency) after around 5 minutes.
I understand the snapshot is stored in s3. But why t1 works fine but t2 is super slow?
UPDATE: I checked manually and found out the composite index is there. I suspect that my composite index for t2 doesn't even work.
And I don't think it's the issue of memory buffer, otherwise, the t2 should become normal after my 5 minutes intensive query test.
