1

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.

10
  • Which version of MariaDB?
    – Rick James
    Jan 28 at 1:36
  • @RickJames 1:10.11.6+maria~ubu2204 apt-get install -y --no-install-recommends mariadb-server="$MARIADB_VERSION" mariadb-backup socat Jan 28 at 1:38
  • See also jira.mariadb.org/browse/MDEV-27653
    – Rick James
    Jan 28 at 1:38
  • 1.10.11.6 does not sound like a valid version.
    – Rick James
    Jan 28 at 1:39
  • 1
    From the last comment in jira.mariadb.org/browse/MDEV-27653 "it's a complain that the optimizer doesn't use an index to optimize the query. This is correct, optimizer doesn't, this is not implemented (yet). But the UNIQUE isn't ignored, the uniqueness constraint holds, it applies on inserts and updates. But the optimizer cannot use it for SELECT." Jan 29 at 9:43

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.