mysql explain in a union query

Question

Hi,

After performing EXPLAIN on a query:

explain 
select name from t1 where name like '%smthing%'
UNION ALL
select name from t2 where name like '%smthing%'
UNION ALL
select name from t3 where name like '%smthing%'
UNION ALL
select name from t4 where name like '%smthing%'

composed by the UNION of 4 tables I get this:

 id  select_type   table         type    possible_keys  key     key_len  ref         rows  Extra                   
 1  PRIMARY       t1             index   (NULL)         name    152      (NULL)      337  Using where; Using index
 2  UNION         t2             index   (NULL)         name    152      (NULL)    3842  Using where; Using index
 3  UNION         t3             index   (NULL)         name    452      (NULL)     204  Using where; Using index
 4  UNION         t4             index   (NULL)         name    452      (NULL)    8269  Using where; Using index
(NULL)  UNION RESULT  <union1,2,3,4>  ALL     (NULL)         (NULL)  (NULL)   (NULL)  (NULL)

When each component of the union is explained, the types are "INDEX" however, union result's type is "ALL". Which is the reason for this behavior? Thanks

Answer 1

Try using UNION ALL instead of UNION - otherwise MySQL will try to remove any duplicate rows (presumably minus the table indecies, as it's looking at the result set).

Also, do you have an ORDER BY or WHERE clause on the result of the UNION? Again, this will be performed on the result set, rather than at the table level.