[MDEV-6904] index_merge intersect(index_that_covers_all_columns, index_that_covers_some) Created: 2014-10-21  Updated: 2018-01-16

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5.39
Fix Version/s: 10.2

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 1
Labels: index_merge, optimizer

Issue Links:
Duplicate
is duplicated by MDEV-13347 MariaDB is not picking up encompassin... Confirmed

 Description   

From jkavalik on #maria:

index_merge/intersection plan is generated even when one index covers all columns:

explain SELECT sql_no_cache SUM(win_real - castka) AS realWinNetto, SUM(castka) AS stake FROM ticket WHERE user_id=286 AND vyplacen=1 GROUP BY user_id;
 
+------+-------------+--------+-------------+-----------------------------------+--------------------------+---------+------+-------+--------------------------------------------------------+
 
| id   | select_type | table  | type        | possible_keys                     | key                      | key_len | ref  | rows  | Extra                                                  |
 
+------+-------------+--------+-------------+-----------------------------------+--------------------------+---------+------+-------+--------------------------------------------------------+
 
|    1 | SIMPLE      | ticket | index_merge | FK_ticket_1,vyplacen,realWinNetto | FK_ticket_1,realWinNetto | 4,6     | NULL | 44496 | Using intersect(FK_ticket_1,realWinNetto); Using where |
 
+------+-------------+--------+-------------+-----------------------------------+--------------------------+---------+------+-------+--------------------------------------------------------+
 

show index from ticket; (15 non-relevant indexes removed)
 
+--------+------------+---------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| Table  | Non_unique | Key_name                  | Seq_in_index | Column_name               | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 
+--------+------------+---------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| ticket |          0 | PRIMARY                   |            1 | ticket_id                 | A         |     2208874 |     NULL | NULL   |      | BTREE      |         |               |
 
| ticket |          1 | FK_ticket_1               |            1 | user_id                   | A         |       26937 |     NULL | NULL   |      | BTREE      |         |               |
 
| ticket |          1 | vyplacen                  |            1 | vyplacen                  | A         |          16 |     NULL | NULL   |      | BTREE      |         |               |
 
| ticket |          1 | realWinNetto              |            1 | user_id                   | A         |        8398 |     NULL | NULL   |      | BTREE      |         |               |
 
| ticket |          1 | realWinNetto              |            2 | vyplacen                  | A         |       10774 |     NULL | NULL   |      | BTREE      |         |               |
 
+--------+------------+---------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 

explain SELECT sql_no_cache SUM(win_real - castka) AS realWinNetto, SUM(castka) AS stake FROM ticket FORCE INDEX(realWinNetto) WHERE user_id=286 AND vyplacen=1;
 
+------+-------------+--------+------+---------------+--------------+---------+-------------+-------+-------+
 
| id   | select_type | table  | type | possible_keys | key          | key_len | ref         | rows  | Extra |
 
+------+-------------+--------+------+---------------+--------------+---------+-------------+-------+-------+
 
|    1 | SIMPLE      | ticket | ref  | realWinNetto  | realWinNetto | 6       | const,const | 52762 |       |
 
+------+-------------+--------+------+---------------+--------------+---------+-------------+-------+-------+



 Comments   
Comment by Sergei Petrunia [ 2014-10-21 ] 

explain select count(*) from ticket where user_id=286;
 
+------+-------------+--------+------+--------------------------+-------------+---------+-------+-------+-------------+
 
| id   | select_type | table  | type | possible_keys            | key         | key_len | ref   | rows  | Extra       |
 
+------+-------------+--------+------+--------------------------+-------------+---------+-------+-------+-------------+
 
|    1 | SIMPLE      | ticket | ref  | FK_ticket_1,realWinNetto | FK_ticket_1 | 4       | const | 47566 | Using index |
 
+------+-------------+--------+------+--------------------------+-------------+---------+-------+-------+-------------+
 
1 row in set (0.00 sec)
 

 
explain select count(*) from ticket where vyplacen=1;
 
+------+-------------+--------+------+---------------+----------+---------+-------+---------+-------------+
 
| id   | select_type | table  | type | possible_keys | key      | key_len | ref   | rows    | Extra       |
 
+------+-------------+--------+------+---------------+----------+---------+-------+---------+-------------+
 
|    1 | SIMPLE      | ticket | ref  | vyplacen      | vyplacen | 2       | const | 1023228 | Using index |
 
+------+-------------+--------+------+---------------+----------+---------+-------+---------+-------------+
 

describe ticket; (51 rows, decimal, int, datetime...)
 
+----------------------------+------------------------+------+-----+---------------------+-------+
 
| Field                      | Type                   | Null | Key | Default             | Extra |
 
+----------------------------+------------------------+------+-----+---------------------+-------+
 
| user_id                    | int(10) unsigned       | NO   | MUL | 0                   |       |
 
| vyplacen                   | smallint(5) unsigned   | NO   | MUL | 0                   |       |
 
+----------------------------+------------------------+------+-----+---------------------+-------+

Comment by Brad Jorgensen [ 2016-07-05 ]

I have this same problem in 10.1.11.

This is a sample I have handy, but the same thing happens with more complex queries selecting more columns and filtering on other columns, too.

SELECT file_type FROM contacts WHERE acctID = '197' AND company_id IN(2252)
 
+------+-------------+----------+-------------+----------------------------+-------------------------+---------+------+------+-------------------------------------------------------+
 
| id   | select_type | table    | type        | possible_keys              | key                     | key_len | ref  | rows | Extra                                                 |
 
+------+-------------+----------+-------------+----------------------------+-------------------------+---------+------+------+-------------------------------------------------------+
 
|    1 | SIMPLE      | contacts | index_merge | company_id,acct_company... | company_id,acct_company | 4,8     | NULL | 8282 | Using intersect(company_id,acct_company); Using where |
 
+------+-------------+----------+-------------+----------------------------+-------------------------+---------+------+------+-------------------------------------------------------+

Both columns are `INT`s. The `company_id` index is on `company_id` and the `acct_company` index is on `acctID, company_id`. According to `mysql.innodb_index_stats`, the estimated cardinalities are 1527 for the `company_id` index and 350, 2320 for the `acct_company` index with approximately 25 million rows in the table. Here are a few more EXPLAIN results that might also help.

SELECT file_type FROM contacts FORCE INDEX (acct_company) WHERE acctID = '197' AND company_id IN(2252)
 
+------+-------------+----------+------+---------------+--------------+---------+-------------+--------+-------+
 
| id   | select_type | table    | type | possible_keys | key          | key_len | ref         | rows   | Extra |
 
+------+-------------+----------+------+---------------+--------------+---------+-------------+--------+-------+
 
|    1 | SIMPLE      | contacts | ref  | acct_company  | acct_company | 8       | const,const | 389408 |       |
 
+------+-------------+----------+------+---------------+--------------+---------+-------------+--------+-------+
 
 
 
SELECT file_type FROM contacts FORCE INDEX (company_id) WHERE acctID = '197' AND company_id IN(2252)
 
+------+-------------+----------+------+---------------+------------+---------+-------+--------+-------------+
 
| id   | select_type | table    | type | possible_keys | key        | key_len | ref   | rows   | Extra       |
 
+------+-------------+----------+------+---------------+------------+---------+-------+--------+-------------+
 
|    1 | SIMPLE      | contacts | ref  | company_id    | company_id | 4       | const | 442400 | Using where |
 
+------+-------------+----------+------+---------------+------------+---------+-------+--------+-------------+
 
 
 
SELECT file_type FROM contacts WHERE acctID = '197'
 
+------+-------------+----------+------+-----------------+--------------+---------+-------+--------+-------+
 
| id   | select_type | table    | type | possible_keys   | key          | key_len | ref   | rows   | Extra |
 
+------+-------------+----------+------+-----------------+--------------+---------+-------+--------+-------+
 
|    1 | SIMPLE      | contacts | ref  | acct_company... | acct_company | 4       | const | 460138 |       |
 
+------+-------------+----------+------+-----------------+--------------+---------+-------+--------+-------+
 
 
 
SELECT file_type FROM contacts WHERE company_id IN(2252)
 
+------+-------------+----------+------+---------------+------------+---------+-------+--------+-------+
 
| id   | select_type | table    | type | possible_keys | key        | key_len | ref   | rows   | Extra |
 
+------+-------------+----------+------+---------------+------------+---------+-------+--------+-------+
 
|    1 | SIMPLE      | contacts | ref  | company_id    | company_id | 4       | const | 442534 |       |
 
+------+-------------+----------+------+---------------+------------+---------+-------+--------+-------+

Generated at Thu Feb 08 07:15:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.