|
MariaDB KB states: "HASH is only supported by the MEMORY storage engine."
Sources:
When we create a table like this:
CREATE TABLE `aaa_device` (
|
`device_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`device_token` varchar(750) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
|
`device_app` varchar(24) NOT NULL,
|
`device_app_version` smallint(5) unsigned DEFAULT NULL,
|
`device_platform` varchar(16) NOT NULL,
|
`device_user_agent` varchar(512) DEFAULT NULL,
|
`device_push_enabled` tinyint(1) unsigned DEFAULT NULL,
|
`device_name` varchar(64) DEFAULT NULL,
|
`pg_id` smallint(5) unsigned DEFAULT NULL,
|
`device_user_last` int(10) unsigned DEFAULT NULL,
|
`device_user_last_logged_in` int(10) unsigned DEFAULT NULL,
|
`device_cookie` text DEFAULT NULL,
|
`device_session_id` varchar(32) DEFAULT NULL,
|
`device_insert` datetime NOT NULL DEFAULT current_timestamp(),
|
`device_update` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
`device_restart` datetime DEFAULT NULL,
|
`device_use` datetime NOT NULL DEFAULT current_timestamp(),
|
`device_cookie_len` int(10) unsigned GENERATED ALWAYS AS (octet_length(`device_cookie`)) STORED,
|
PRIMARY KEY (`device_id`),
|
UNIQUE KEY `ucTokenApp` (`device_token`,`device_app`),
|
KEY `device_app_device_app_version` (`device_app`,`device_app_version`),
|
KEY `device_user_last` (`device_user_last`),
|
KEY `device_user_last_logged_in` (`device_user_last_logged_in`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
|
;
|
|
MariaDB converts this to this:
SHOW CREATE TABLE aaa_device\G
CREATE TABLE `aaa_device` (
|
`device_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`device_token` varchar(750) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
|
`device_app` varchar(24) NOT NULL,
|
`device_app_version` smallint(5) unsigned DEFAULT NULL,
|
`device_platform` varchar(16) NOT NULL,
|
`device_user_agent` varchar(512) DEFAULT NULL,
|
`device_push_enabled` tinyint(1) unsigned DEFAULT NULL,
|
`device_name` varchar(64) DEFAULT NULL,
|
`pg_id` smallint(5) unsigned DEFAULT NULL,
|
`device_user_last` int(10) unsigned DEFAULT NULL,
|
`device_user_last_logged_in` int(10) unsigned DEFAULT NULL,
|
`device_cookie` text DEFAULT NULL,
|
`device_session_id` varchar(32) DEFAULT NULL,
|
`device_insert` datetime NOT NULL DEFAULT current_timestamp(),
|
`device_update` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
`device_restart` datetime DEFAULT NULL,
|
`device_use` datetime NOT NULL DEFAULT current_timestamp(),
|
`device_cookie_len` int(10) unsigned GENERATED ALWAYS AS (octet_length(`device_cookie`)) STORED,
|
PRIMARY KEY (`device_id`),
|
UNIQUE KEY `ucTokenApp` (`device_token`,`device_app`) USING HASH,
|
KEY `device_app_device_app_version` (`device_app`,`device_app_version`),
|
KEY `device_user_last` (`device_user_last`),
|
KEY `device_user_last_logged_in` (`device_user_last_logged_in`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
|
;
|
Note: UNIQUE KEY `ucTokenApp` (`device_token`,`device_app`) USING HASH,
So far so good, I can live with it... But: After inserting some data:
Error formatting macro: code: java.lang.StackOverflowError
INSERT INTO `aaa_device` (`device_id`, `device_token`, `device_app`, `device_app_version`, `device_platform`, `device_user_agent`, `device_push_enabled`, `device_name`, `pg_id`, `device_user_last`, `device_user_last_logged_in`, `device_cookie`, `device_session_id`, `device_insert`, `device_update`, `device_restart`, `device_use`, `device_cookie_len`)
VALUES
(5, 'cpM6TlH5-VI:APA91bG5gEyiw8psmZGQzYCPFgYQU0TNsCFb5jeivYkLybTlL5p6844yrqBZVsGZENsSxTvZwb9MyTItT5oHz0eCWjvC42oixOm6HIKg-IPPi-Mt1bJRTdd0p-0FVKzppC3BTAvqITzg', 'pcco', NULL, 'ios', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2020-02-19 13:28:25', '2022-07-20 19:05:49', NULL, '2022-07-16 14:39:04', NULL)
, (8, 'dJMAAFywpGk:APA91bELVRD74PdZG5Vd8flXGeE0teS3iEzTL3vaW17S4CCBSeNQpb1CPU32s7fGfa35KLEQEuq1-W8SWssghQu26u5uDPrHiMGzHjisnGKZp_7l5F3q_uMAtZEjqUioHiGCFXws-r0v', 'pcco', 202, 'ios', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148', 1, 'Apple-iPad5,3', 679, 43204, 43204, '{\"auth\":
{\"authBy\":\"login\",\"login\":\"hans.meier@t-online.de\",\"password\":\"402c9fe50e9e3ff6dc01742df5fff05c\"}
,\"club\":\"129\",\"device_id\":\"8\",\"lang\":\"de\",\"visitor_uid\":\"79f3eca97c2ad28230521288429f8bf8\"}', 'vf3cvvBb1hZmDx7sVaDl2wUPYV', '2020-02-19 13:28:53', '2023-04-16 08:31:36', '2023-04-16 08:30:50', '2023-04-16 08:31:36', 200)
, (11, 'dQHXAXyQFwM:APA91bG7pY8qOtHdLTKJ41uhi87mwBEy2RybQyHy1nLNyGAW7oBWE4TZZ2HHhLabLxtRHmk1CBMibLOGeKp0M1sGGB-BkCqFdZ1UImvxcI3BXFuUO4V3PrN8M2VdFeKHkPYnkXeypoJC', 'pcco', NULL, 'ios', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2020-02-19 13:29:00', '2022-07-20 19:05:49', NULL, '2022-07-16 14:39:04', NULL)
, (14, 'edtFfXjgpRk:APA91bEKHLjwAW2RWb7dMwWgT3eRlmyaWYMlbXLhLrlkhFj2dgh-hOp3lb1nLMgEEqXwV6k_zb5qduMQcZoSLDo-NmYjvdl2CIeyHzElhBRbmGo5RE-WFH5oL6JacgJ-wZEhEWlOzjaU', 'pcco', NULL, 'android', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2020-02-19 13:29:10', '2022-07-20 19:05:49', NULL, '2022-07-16 14:39:04', NULL)
, (17, 'evOBMlbektw:APA91bFfkMTua4aYNcOxNpjmVCq9VCMnqvJjC5bll5cxpQR9MT7BKdMeOYXj1kBYYpPLEUgRZcmXfD9kguu0kvnybmbC4jaW86s59ynlKXAuKjT3eGQ2Pk7pWc_gs6eIrFAyYAkf8_wL', 'pcco', NULL, 'android', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2020-02-19 13:29:11', '2022-07-20 19:05:49', NULL, '2022-07-16 14:39:04', NULL)
, (20, 'eGx98rjhVa4:APA91bEdCz9XGtmhasDlXnUilCFiBE2KHyc6oAHqvAITAaDd-e-ntnlM5rrqo7KY7BZapBh26rsWW_zy3T0wIwf2nAnEEoyrXXrDoLogBZ0GcBvSJi9mdEKfWot4XusqYZv11JI_ldah', 'pcco', NULL, 'android', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2020-02-19 13:29:12', '2022-07-20 19:05:49', NULL, '2022-07-16 14:39:04', NULL)
, (23, 'f5cBwRzHlcE:APA91bFsms_dQ1XY5NjjrTq-y0nzqArkR7u7mZ02IA5fejxsDWmoTZbclkzcRaNzRO3LEqMfQXGJZG-tswAnOKtgk2If73sAqezXOA_azqem4JjtSwONjYOYeEHqdbzeiQBuqJoladXS', 'pcco', 202, 'ios', 'Mozilla/5.0 (iPhone; CPU iPhone OS 15_7_3 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148', 1, 'Apple-iPhone8,4', 679, 433213, 433213, '{\"auth\":
{\"authBy\":\"login\",\"login\":\"fritz.müller@web.de\",\"password\":\"6469e8701df05ecd7736343296a383a8\"}
,\"club\":\"694\",\"device_id\":\"23\",\"lang\":\"de\",\"visitor_uid\":\"b7519c67ad0320908d418c5a05ef3dfb\"}', 'ulBUTArKfWqKOwUWeD7lEGC4nW', '2020-02-19 13:29:18', '2023-02-14 17:01:02', '2023-02-14 17:00:26', '2023-02-14 17:01:02', 187)
;
The Query Execution Plan looks completely wrong:
EXPLAIN
|
SELECT * FROM aaa_device
|
WHERE device_app = 'pcco'
|
AND device_token = 'f5cBwRzHlcE:APA91bFsms_dQ1XY5NjjrTq-y0nzqArkR7u7mZ02IA5fejxsDWmoTZbclkzcRaNzRO3LEqMfQXGJZG-tswAnOKtgk2If73sAqezXOA_azqem4JjtSwONjYOYeEHqdbzeiQBuqJoladXS'
|
;
|
+------+-------------+------------+------+------------------------------------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+------------------------------------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | aaa_device | ALL | ucTokenApp,device_app_device_app_version | NULL | NULL | NULL | 7 | Using where |
|
+------+-------------+------------+------+------------------------------------------+------+---------+------+------+-------------+
|
After changing the field length of device_token from 750 to 700 VARCHAR:
ALTER TABLE aaa_device MODIFY COLUMN `device_token` varchar(700) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;
|
The USING HASH disappeared! And the Optimizer did the right choice:
|
EXPLAIN
|
SELECT * FROM aaa_device
|
WHERE device_app = 'pcco'
|
AND device_token = 'f5cBwRzHlcE:APA91bFsms_dQ1XY5NjjrTq-y0nzqArkR7u7mZ02IA5fejxsDWmoTZbclkzcRaNzRO3LEqMfQXGJZG-tswAnOKtgk2If73sAqezXOA_azqem4JjtSwONjYOYeEHqdbzeiQBuqJoladXS'
|
;
|
+------+-------------+------------+-------+------------------------------------------+------------+---------+-------------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+-------+------------------------------------------+------------+---------+-------------+------+-------+
|
| 1 | SIMPLE | aaa_device | const | ucTokenApp,device_app_device_app_version | ucTokenApp | 2900 | const,const | 1 | |
|
+------+-------------+------------+-------+------------------------------------------+------------+---------+-------------+------+-------+
|
So either there is something wrong in the documentation and/or the Optimizer does something really odd...
Tested it reproducible with 10.11.2 and 10.6.11.
Bonus information:
SQL> INSERT INTO aaa_device (device_id, device_token, device_app, device_platform) select device_id, device_token, device_app, device_platform from test.aaa_device limit 100;
|
ERROR 1364 (HY000): Field 'DB_ROW_HASH_1' doesn't have a default value
|
|
|