title: Why would MySQL use index intersection instead of combined index?
body: <p>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:</p>
  
  <pre><code>SELECT id FROM tbl
  WHERE rel = 3 AND type = 3
  ORDER BY created;
  </code></pre>
  
  <p>would be to use index <code>(type, rel, created)</code>.
  But MySQL decides to intersect indexes <code>(type)</code> and <code>(rel)</code>, and that leads to worse perfomance. Here is an example:</p>
  
  <pre><code>mysql&gt; EXPLAIN
      -&gt; SELECT id FROM tbl
      -&gt; WHERE rel = 3 AND type = 3
      -&gt; 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
  </code></pre>
  
  <p>And the same query, but with a hint added:</p>
  
  <pre><code>mysql&gt; EXPLAIN
      -&gt; SELECT id FROM tbl USE INDEX (idx_type_rel_created)
      -&gt; WHERE rel = 3 AND type = 3
      -&gt; 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
  </code></pre>
  
  <p>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?</p>
  
  <pre><code>mysql&gt; SELECT COUNT(*) FROM tbl WHERE type=3 AND rel=3;
  +----------+
  | COUNT(*) |
  +----------+
  |     3056 |
  +----------+
  </code></pre>
  
  <p>From this I can conclude that MySQL is mistaken at calculating approximate number of rows for combined index.</p>
  
  <p>So, what can I do here to make MySQL take the right execution plan?</p>
  
  <p>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.</p>
  
  <p>MySQL version is 5.1.49. </p>
  
  <p>The table structure is:</p>
  
  <pre><code>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;
  </code></pre>