MySQL: Usage of indices in UNION subselects

Question

In MySQL 5.0.75-0ubuntu10.2 I've got a fixed table layout like that:

Table parent with an id Table parent2 with an id Table children1 with a parentId

CREATE TABLE  `Parent` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(200) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB
CREATE TABLE  `Parent2` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(200) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB
CREATE TABLE  `Children1` (
  `id` int(11) NOT NULL auto_increment,
  `parentId` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `parent` (`parentId`)
) ENGINE=InnoDB

A children has a parent in one of the tables Parent or Parent2. When I need to get a children I use a query like that:

select * from Children1 c 
inner join (
select id as parentId from Parent
union 
select id as parentId from Parent2
) p on p.parentId = c.parentId

Explaining this query yields:

+----+--------------+------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------+
| id | select_type  | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                                               |
+----+--------------+------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------+
|  1 | PRIMARY      | NULL       | NULL  | NULL          | NULL    | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables | 
|  2 | DERIVED      | Parent     | index | NULL          | PRIMARY | 4       | NULL |    1 | Using index                                         | 
|  3 | UNION        | Parent2    | index | NULL          | PRIMARY | 4       | NULL |    1 | Using index                                         | 
| NULL | UNION RESULT | <union2,3> | ALL   | NULL          | NULL    | NULL    | NULL | NULL |                                                     | 
+----+--------------+------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------+
4 rows in set (0.00 sec)

which is reasonable given the layout.

Now the problem: The previous query is somewhat useless, since it returns no columns from the parent elements. In the moment I add more columns to the inner query no index will be used anymore:

mysql> explain select * from Children1 c  inner join ( select id as parentId,name from Parent union  select id as parentId,name from Parent2 ) p on p.parentId = c.parentId;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables | 
|  2 | DERIVED      | Parent     | ALL  | NULL          | NULL | NULL    | NULL |    1 |                                                     | 
|  3 | UNION        | Parent2    | ALL  | NULL          | NULL | NULL    | NULL |    1 |                                                     | 
| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                                                     | 
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
4 rows in set (0.00 sec)

Can anyone explain why the (PRIMARY) indices are not used any more? Is there a workaround for this problem if possible without having to change the DB layout?

Thanks!

Answer 1

My first thought is to insert a "significant" number of records in the tables and use ANALYZE TABLE to update the statistics. A table with 4 records will always be faster to read using a full scan rather then going via the index! Further, you can try USE INDEX to force the usage of the index and look how the plan changes.

I will also recomend reading this documentation and see which bits are relevant MYSQL::Optimizing Queries with EXPLAIN

This article can also be useful 7 ways to convince MySQL to use the right index

Answer 2

I think that the optimizer falls down once you start pulling out multiple columns in the derived query because of the possibility that it would need to convert data types on the union (not in this case, but in general). It may also be due to the fact that your query essentially wants to be a correlated derived subquery, which isn't possible (from dev.mysql.com):

Subqueries in the FROM clause cannot be correlated subqueries, unless used within the ON clause of a JOIN operation.

What you are trying to do (but isn't valid) is:

select * from Children1 c 
inner join (
select id as parentId from Parent where Parent.id = c.parentId
union 
select id as parentId from Parent2 where Parent.id = c.parentId
) p 

Result: "Unknown column 'c.parentId' in 'where clause'.

Is there a reason you don't prefer two left joins and IFNULLs:

select *, IFNULL(p1.name, p2.name) AS name from Children1 c
left join Parent p1 ON p1.id = c.parentId
left join Parent2 p2 ON p2.id = c.parentId

The only difference between the queries is that in yours you'll get two rows if there is a parent in each table. If that's what you want/need then this will work well also and joins will be fast and always make use of the indexes:

(select * from Children1 c join Parent p1 ON p1.id = c.parentId)
union
(select * from Children1 c join Parent2 p2 ON p2.id = c.parentId)