I have the following table:
| s3entry | CREATE TABLE s3entry
(
ID
bigint(20) NOT NULL AUTO_INCREMENT,
S3KEY
varchar(1024) DEFAULT NULL,
FETCHESSINCELASTS3FETCH
int(11) DEFAULT NULL,
inS3Store
tinyint(4) DEFAULT 0,
inLocalCache
tinyint(4) DEFAULT 0,
PRIMARY KEY (ID
),
UNIQUE KEY s3key
(S3KEY
) USING HASH,
KEY inS3Store_inLocalCache_lastcachefetch
(inS3Store
,inLocalCache
,LASTCACHEFETCH
),
KEY inLocalCache_lastfetch
(inLocalCache
,LASTCACHEFETCH
),
KEY cache_size
(inLocalCache
,inS3Store
,size
)
) ENGINE=InnoDB AUTO_INCREMENT=5847320 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
The table has some 5 million records.
When I run the following query it ignores the s3key index:
explain SELECT * FROM s3entry force index (s3key) WHERE s3key = 'some long text of about 400 bytes that exactly matches an s3key in the db';
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | s3entry | ALL | s3key | NULL | NULL | NULL | 5570486 | Using where |
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.000 sec)
Given the exact match and the simple where clause I'm expecting mariadb to use the key.
I've also tried explicitly passing the collation in the query:
MariaDB [op]> explain SELECT * FROM s3entry WHERE s3key collate utf8mb4_unicode_ci = 'some long text of about 400 bytes that exactly matches an s3key in the db';
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | s3entry | ALL | NULL | NULL | NULL | NULL | 5763585 | Using where |
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.001 sec)
I can solve the problem by adding an index to the table on the same key, but this seems unnecessary:
s3entry | CREATE TABLE s3entry
(
ID
bigint(20) NOT NULL AUTO_INCREMENT,
CREATEDAT
datetime(3) DEFAULT NULL,
guid
varchar(255) NOT NULL,
LASTS3FETCH
date DEFAULT NULL,
size
bigint(20) DEFAULT NULL,
LASTCACHEFETCH
date DEFAULT NULL,
S3KEY
varchar(1024) DEFAULT NULL,
FETCHESSINCELASTS3FETCH
int(11) DEFAULT NULL,
inS3Store
tinyint(4) DEFAULT 0,
inLocalCache
tinyint(4) DEFAULT 0,
PRIMARY KEY (ID
),
UNIQUE KEY s3key
(S3KEY
) USING HASH,
KEY inS3Store_inLocalCache_lastcachefetch
(inS3Store
,inLocalCache
,LASTCACHEFETCH
),
KEY inLocalCache_lastfetch
(inLocalCache
,LASTCACHEFETCH
),
KEY cache_size
(inLocalCache
,inS3Store
,size
),
KEY s3key2
(S3KEY
(768))
) ENGINE=InnoDB AUTO_INCREMENT=5856082 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
explain SELECT * FROM s3entry WHERE s3key = 'some long text of about 400 bytes that exactly matches an s3key in the db';
+------+-------------+---------+------+---------------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | s3entry | ref | s3key,s3key2 | s3key2 | 3075 | const | 1 | Using where |
+------+-------------+---------+------+---------------------+--------+---------+-------+------+-------------+
1 row in set (0.000 sec)
I don't understand why the hash won't work.
Asked by Brett Sutton
(111 rep)
Jan 27, 2024, 12:11 PM