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

explain shows index used, who's fields aren't part of the query

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.4.4
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
      None

      Description

      From: https://dba.stackexchange.com/questions/238264/mariadb-10-4-4-exists-problem

      CREATE TABLE `accounts` (
           `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
           `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
           `group_id` int(11) NOT NULL DEFAULT 2,
           PRIMARY KEY (`id`),
           KEY `name` (`name`),
           KEY `group_id` (`group_id`),
          ) ENGINE=InnoDB AUTO_INCREMENT=33261 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
       
      CREATE TABLE `table1` (
       `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
       `order_id` int(11) NOT NULL,
       `order_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
       `member_id` int(11) NOT NULL,
       `date` int(11) NOT NULL,
       `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
       `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
       PRIMARY KEY (`id`),
       KEY `table1_order_id_index` (`order_id`),
       KEY `member_id_2` (`member_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=1985597 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
      

      SELECT *
      FROM `table1`
      WHERE (`order_id` = 1234
             OR (EXISTS
                   (SELECT *
                    FROM `accounts`
                    WHERE `table1`.`member_id` = `accounts`.`id` )
                 AND EXISTS
                   (SELECT *
                    FROM `accounts`
                    WHERE `accounts`.`name` = 'test' )))
      ORDER BY `table1`.`id`
      

      +------+--------------+-----------+-------+--------------------------+----------+---------+-------+---------+--------------------------+
      | id   | select_type  | table     | type  | possible_keys            | key      | key_len | ref   | rows    | Extra                    |
      +------+--------------+-----------+-------+--------------------------+----------+---------+-------+---------+--------------------------+
      |    1 | PRIMARY      | table1 | index | table1_order_id_index | PRIMARY  | 4       | NULL  | 1965346 | Using where              |
      |    3 | SUBQUERY     | accounts  | ref   | name                     | name     | 767     | const | 1       | Using where; Using index |
      |    2 | MATERIALIZED | accounts  | index | PRIMARY,id          | group_id | 4       | NULL  | 32558   | Using index              |----------+----------+---------+-------+---------+--------------------------+
      

      group_id isn't part of the query however it was the key chosen for the query.

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              danblack Daniel Black
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: