Comparison of the execution efficiency of the SQL statement or with UNION ALL

See an article is about the SQL statement or the execution of Union all efficiency comparison, did not pay much attention to this problem, feel the article is good, turn to see.

Article original link: http://www.cunyoulu.com/zhuanti/qtstudy/20081124orunion.htm

Comparison of the execution efficiency of the SQL statement or with UNION ALL

When a SQL statement has multiple or statements, consider using union or union all instead to increase the speed. SQL statements that use or are often unable to be optimized, resulting in slower speeds. But it's not fixed, and sometimes it's faster to use or. The specific situation is subject to test. If indexed, speed optimization can also be achieved.

The experimental table below, the actual data has 2,000,000, from the inside to return about a maximum of about 1000 rows of data.

x	y	inline	CDP	t
12002400
12002408	5801005	300	300	300		3406
...	...

or statement (partial excerpt)

SELECT * FROM TableName where (cdp= and inline=301) or (cdp= 301 and inline=301) or (cdp= 302 and inline=301) or (cdp= 303 and inline=301) or (cdp= 304 and inline=301) or (cdp= 305 and inline=301) or (cdp= 306 and inline=301) or (cdp= 307 a nd inline=301)

UNION ALL statement (partial excerpt)

SELECT * FROM TableName where (inline= and cdp=300) UNION ALL SELECT * from TableName where (inline= 301 and cdp=300) UNION ALL SELECT * FROM TableName where (inline= 302 and cdp=300) union ALL SELECT * from TableName where (inline= 303 and CDP=300)

Return irregular 900 data, the former used for more than 60 seconds, the latter took 8 seconds or so.

Comparison of the execution efficiency of the SQL statement or with UNION ALL