CODEWITHSUNDEEP
mysqlquery-optimizationsql-execution-plandatabase-indexes
Ivan Virabyan
Ivan Virabyan

Reputation: 1694

Why would MySQL use index intersection instead of combined index?

From time to time I encounter a strange MySQL behavior. Let's assume I have indexes (type, rel, created), (type), (rel). The best choice for a query like this one:

SELECT id FROM tbl
WHERE rel = 3 AND type = 3
ORDER BY created;

would be to use index (type, rel, created). But MySQL decides to intersect indexes (type) and (rel), and that leads to worse perfomance. Here is an example:

mysql> EXPLAIN
    -> SELECT id FROM tbl
    -> WHERE rel = 3 AND type = 3
    -> ORDER BY created\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl
         type: index_merge
possible_keys: idx_type,idx_rel,idx_rel_type_created
          key: idx_type,idx_rel
      key_len: 1,2
          ref: NULL
         rows: 4343
        Extra: Using intersect(idx_type,idx_rel); Using where; Using filesort

And the same query, but with a hint added:

mysql> EXPLAIN
    -> SELECT id FROM tbl USE INDEX (idx_type_rel_created)
    -> WHERE rel = 3 AND type = 3
    -> ORDER BY created\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl
         type: ref
possible_keys: idx_type_rel_created
          key: idx_type_rel_created
      key_len: 3
          ref: const,const
         rows: 8906
        Extra: Using where

I think MySQL takes an execution plan which contains less number in the "rows" column of the EXPLAIN command. From that point of view, index intersection with 4343 rows looks really better than using my combined index with 8906 rows. So, maybe the problem is within those numbers?

mysql> SELECT COUNT(*) FROM tbl WHERE type=3 AND rel=3;
+----------+
| COUNT(*) |
+----------+
|     3056 |
+----------+

From this I can conclude that MySQL is mistaken at calculating approximate number of rows for combined index.

So, what can I do here to make MySQL take the right execution plan?

I can not use optimizer hints, because I have to stick to Django ORM The only solution I found yet is to remove those one-field indexes.

MySQL version is 5.1.49.

The table structure is:

CREATE TABLE tbl (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` tinyint(1) NOT NULL,
  `rel` smallint(2) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type`),
  KEY `idx_rel` (`rel`),
  KEY `idx_type_rel_created` (`type`,`rel`,`created`)
) ENGINE=MyISAM;

Upvotes: 8

Views: 14337

Answers (3)

Quassnoi
Quassnoi

Reputation: 425723

It's hard to tell exactly why MySQL chooses index_merge_intersection over the index scan, but you should note that with composite indexes, statistics for a given column are stored for the leading part of the index up to and including the column, not just for the column itself.

The value of information_schema.statistics.cardinality for the column type of the composite index will show the cardinality of (rel, type), not that of type.

If there is a correlation between rel and type, then the cardinality of (rel, type) will be smaller than the product of real cardinalities of rel and type.

That's why the number of rows is calculated incorrectly (an intersection cannot be larger in size than a union).

You can turn off index_merge_intersection by setting it to off in @@optimizer_switch:

SET optimizer_switch = 'index_merge_intersection=off'

Upvotes: 17

Sophie Sewan
Sophie Sewan

Reputation: 1

Some time the intersection on same table could be interesting, and you may not want to remove an index on a single colum so as some other query work well with intersection. In such case, if the bad execution plan concerns only one single query, a solution is to exclude the unwanted index. Il will then prevent the usage of intersection only for that sepcific query... In your example :

SELECT id FROM tbl IGNORE INDEX(idx_type) 
WHERE rel = 3 AND type = 3
ORDER BY created;
enter code here

Upvotes: 0

newtover
newtover

Reputation: 32094

Another thing is worth mentioning: you would not have the problem if you deleted the index on type only. the index is not required since it duplicates a part of the composite index.

Upvotes: 4

Related Questions