title: UNION DISTINCT faster than OR on 9 million records?
body: <p>I have an InnoDB table with +- 9 million records.</p>
  
  <p>I used to have a query like this</p>
  
  <pre><code>SELECT
    my_primary_key
  FROM
    my_table
  WHERE
    col1 = 1 AND
    col2 = 2 AND
    (col3 = 'aaa' OR col4 = 'bbb' OR col5 = 'ccc')
  </code></pre>
  
  <p>My table structure :</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `my_table` (
    `my_primary_key` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `col1` tinyint(1) unsigned NOT NULL DEFAULT '0',
    `col2` tinyint(1) unsigned NOT NULL DEFAULT '0',
    `col3` varchar(255) NOT NULL,
    `col4` varchar(255) NOT NULL,
    `col5` varchar(255) NOT NULL,
    PRIMARY KEY (`my_primary_key`),
    KEY `col1` (`col1`),
    KEY `col2` (`col2`),
    KEY `col3` (`col3`),
    KEY `col4` (`col4`),
    KEY `col5` (`col5`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>Based on my friend suggestion, i tried to change it into</p>
  
  <pre><code>SELECT my_primary_key FROM my_table WHERE col1 = 1 AND col2 = 2 AND col3 = 'aaa'
  UNION DISTINCT
  SELECT my_primary_key FROM my_table WHERE col1 = 1 AND col2 = 2 AND col4 = 'bbb'
  UNION DISTINCT
  SELECT my_primary_key FROM my_table WHERE col1 = 1 AND col2 = 2 AND col5 = 'ccc'
  </code></pre>
  
  <p>But when i tried to see the running queries, i see that the "time" is still quite high (between 8-20 seconds)</p>
  
  <pre><code>SHOW FULL PROCESSLIST
  </code></pre>
  
  <p>Am i on the correct path by using UNION DISTINCT ? Or is there other faster way to execute my query?</p>
  
  <p>Thanks.</p>