mysql union

两种方式加速UNIONs 

1.用union all  而不是union（允许的话） 

UNION ALL is much faster than UNION

union 原理：

表一

[code]
blue
green
gray
black
[/code]

表二：

[code]
red
green
yellow
blue
[/code]

 UNION两表；

mysql首先会排序俩表数据到临时表；

 

[code]
black
blue
blue
gray
green
green
red
yellow
[/code]

然后去重

[code]
black
blue
gray
green
red
yellow
[/code]

See also: Mythical MySQL DBA – the talent drought.

 UNION ALL则是

[code]
blue
green
gray
black
red
green
yellow
blue
[/code]

不用排序|不用去重|

 .

2. Use Push-down Conditions to speedup UNION in MySQL

 表一

[code]
blue 2013
green 2013
green 2012
gray 2011
black 2009
black 2011
[/code]

表二

[code]
red 2012
red 2013
green 2011
yellow 2010
blue 2011
[/code]

查找2013年数据

[code]
(SELECT type, release FROM short_sleeve)
UNION
(SELECT type, release FROM long_sleeve);
WHERE release >=2013;
[/code]

See also: 5 More Things Deadly to Scalability and the original 5 Things Toxic to Scalability..

Here the WHERE clause works on this 11 record temp table:

[code]
black 2009
black 2011
blue 2011
blue 2013
gray 2011
green 2013
green 2012
green 2011
red 2012
red 2013
yellow 2010
[/code]

But it would be much faster to move the WHERE inside each subquery like this:

如果放进里面：速度会快很多

[code]
(SELECT type, release FROM short_sleeve WHERE release >=2013)
UNION
(SELECT type, release FROM long_sleeve WHERE release >=2013);
[/code]

 

转载于:https://my.oschina.net/u/3255899/blog/1486580