UNION vs GROUP BY or better solution

Question

I have a situation where I have some hundred of thousands of rows in database table with, let's say 8 columns, where first two columns indexed (two indexes for each column, and one composite index for both columns), and I have two SQL queries with group by and union like:

SELECT MIN(columnOne), columnTwo FROM MyTable
WHERE columnTwo IN (1,2,3)
GROUP BY columnTwo

and

SELECT MIN(columnOne), columnTwo FROM MyTable WHERE columnTwo = 1
UNION
SELECT MIN(columnOne), columnTwo FROM MyTable WHERE columnTwo = 2
UNION
SELECT MIN(columnOne), columnTwo FROM MyTable WHERE columnTwo = 3

And it seems that second approach with unions works two(sometimes more) times faster than first one.

I'm executing this queries in Python so the first one is one liner, and second one I need to generate.

I wonder if second approach is normal, and may be there is third way that I don't know of?

UPDATE:

columnTwo and columnOne fields in all queries are not unique

Example

# columnOne columnTwo
1 a         a        
2 b         b        
3 c         b        
4 d         a        
...

Explain for query with group by shows this:

id  select_type    table        type    possible_keys               key       key_len           ref     rows    Extra
1   SIMPLE         MyTable      index   secondColIndex,bothColIndex bothColIndex    12                 1623713   Using where

Explain for query with unions shows this:

id  select_type    table        type    possible_keys               key       key_len   ref     rows    Extra
1   PRIMARY        MyTable      ref     secondColIndex,bothColIndex bothColIndex    4   const   217472  Using where
2   UNION          MyTable      ref     secondColIndex,bothColIndex bothColIndex    4   const   185832  Using where
3   UNION          MyTable      ref     secondColIndex,bothColIndex bothColIndex    4   const   175572  Using where
    UNION RESULT    ALL                                     Using temporary

Indexes in MyTable:

Table, Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment
MyTable, 0, PRIMARY, 1, Id, A, 1623713, , , , BTREE, , 
MyTable, 1, columnOneIndex, 1, columnOne, A, 1623713, , , , BTREE, , 
MyTable, 1, columnTwoIndex, 1, columnTwo, A, 5737, , , , BTREE, , 
MyTable, 1, bothColumnsIndex, 1, columnTwo, A, 5171, , , , BTREE, , 
MyTable, 1, bothColumnsIndex, 2, columnOne, A, 1623713, , , , BTREE, ,

user_1144035 · Answer

What you are seeing is due to the limitations of MySQL's optimizer (which may be much improved in the most recent versions). A GROUP BY almost always results in a file sort, limiting the use of indexes.

One alternative is essentially just a simplification of the UNION version, but using correlated subqueries:

SELECT x.columnTwo,
       (SELECT MIN(columnOne)
        FROM myTable t
        WHERE t.columnTwo = x.columnTwo
       ) as min_columnOne
FROM (SELECT 1 as columnTwo UNION ALL
      SELECT 2 as columnTwo UNION ALL
      SELECT 3 as columnTwo
     ) x;

This should have essentially the same performance as your version with UNION. The correlated subquery should use the index for the calculation.

UNION vs GROUP BY or better solution

1 Answers