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
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
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
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
两个语句几乎完全一样,只是select子句的内容不同。运行的结果是,第一个语句需要2分多钟,而第二个语句只需要2秒钟左右。
请高人指点一下这是为什么?
以下是一些背景情况:
1 yp_41中的数据有300多万条;
2 all_keyword中的数据有1万多条;
3 所有在where中涉及的列都已加索引(虽然索引对于like好像没什么作用。如果不加索引,则两个SQL一样的慢。)