两个神奇的SQL语句
请大家一起来看两个SQL语句:
1.
2.
两个语句几乎完全一样,只是select子句的内容不同。运行的结果是,第一个语句需要2分多钟,而第二个语句只需要2秒钟左右。
请高人指点一下这是为什么?
以下是一些背景情况:
1 yp_41中的数据有300多万条;
2 all_keyword中的数据有1万多条;
3 所有在where中涉及的列都已加索引(虽然索引对于like好像没什么作用。如果不加索引,则两个SQL一样的慢。)
1.
1
select top 10 id from yp_41 where
2
(
3
(
4
category in
5
(
6
select categorycode from all_keyword where keyword like '%纸箱%'
7
)
8
and region like '41%'
9
)
10
and category like '%'
11
or
12
(
13
category='00000000' and [name] like '%纸箱%' and region like '41%'
14
)
15
)
16![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
2.
1
select count(*) from yp_41 where
2
(
3
(
4
category in
5
(
6
select categorycode from all_keyword where keyword like '%纸箱%'
7
)
8
and region like '41%'
9
)
10
and category like '%'
11
or
12
(
13
category='00000000' and [name] like '%纸箱%' and region like '41%'
14
)
15
)
16![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
两个语句几乎完全一样,只是select子句的内容不同。运行的结果是,第一个语句需要2分多钟,而第二个语句只需要2秒钟左右。
请高人指点一下这是为什么?
以下是一些背景情况:
1 yp_41中的数据有300多万条;
2 all_keyword中的数据有1万多条;
3 所有在where中涉及的列都已加索引(虽然索引对于like好像没什么作用。如果不加索引,则两个SQL一样的慢。)