mysql在联合查询中解释

After performing EXPLAIN on a query:

对查询执行EXPLAIN后:

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:

由4个表的UNION组成,我得到了这个:

 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

解释联合的每个组件时,类型为“INDEX”,但联合结果的类型为“ALL”。这种行为的原因是什么?谢谢

1 个解决方案

#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).

尝试使用UNION ALL而不是UNION - 否则MySQL将尝试删除任何重复的行(可能会减去表的缺点,因为它正在查看结果集)。

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.

另外,你对UNION的结果有ORDER BY或WHERE子句吗?同样,这将在结果集上执行,而不是在表级执行。

#1