[MDEV-19489] explain shows index used, who's fields aren't part of the query Created: 2019-05-16  Updated: 2019-06-16  Resolved: 2019-06-16

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Igor Babaev
Resolution: Not a Bug Votes: 0
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.

 Comments   
Comment by Igor Babaev [ 2019-05-29 ]

Daniel,
MariaDB transforms 

EXISTS
 
   (SELECT *
 
     FROM accounts
 
     WHERE table1.member_id = accounts.id)

into

member_id IN  (SELECT accounts.id FROM accounts)

Then it materializes the projection of table account on field id.
For this it chooses the cheapest scan that contains id. As account is an InnoDB table each index tuple contains id.

Are you satisfied with the answer?

Comment by Igor Babaev [ 2019-06-16 ]

Close as not a bug: see the explanation in my comment

Generated at Thu Feb 08 08:52:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.