两种方式加速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]