Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-31072

InnoDB is USING HASH and Optimizer is confused

    XMLWordPrintable

Details

    Description

      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:

      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
      
      

      Attachments

        Issue Links

          Activity

            People

              greenman Ian Gilfillan
              oli Oli Sennhauser
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.