Sample Header Ad - 728x90

unique key on varchar is ignored for maria db

1 vote
0 answers
77 views
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