建议你建立索引。先: ALTER TABLE Phone ADD Phone_temp as left(Phone,7)CREATE INDEX phone_phone_temp_index ON phone (Phone_temp) 再:Select a.Phone, b.Area Into PhoneArea From Phone As a, Area as b Where a.Phone_temp between b.Phone1 And b.Phone2
在大力的基础上,再建立一个索引,可能效果更好些:CREATE INDEX Area_Phone1_Phone2_index ON Area (Phone1,b.Phone2)
但我试了试:首先是将Phone表中的纪录处理为7位,类型bigint。 然后将Area中的Phone1,Phone2转换为bigint,这样就是同等类型。re: 你同等类型就不存在转换的问题当然快了。之后如此:Select a.Area, p.Phone From Area as a, Phone as p Where p.Phone Between a.Phone1 And Phone2这样仅运行了几秒钟。但若将Phone1 和 Phone2 都乘10000(将号段转为11位,bigint)。Phone中的字段仍为原来的11位(bigint),这样就运行好多分钟(我没让他运行完)。re: 这又回到开始时你的问题上:Phone1 和 Phone2 都乘10000 你在select 时再乘的话,弊端在:没有用到表的索引,索引用不上! 你有10000条记录就好乘 200000次。你要: ALTER TABLEArea ADD Phone1_temp as Phone1*1000 ALTER TABLEArea ADD Phone2_temp as Phone2*1000CREATE INDEX TABLEArea_Phone1_temp_index ON TABLEArea(Phone1_temp) CREATE INDEX TABLEArea_Phone2_temp_index ON TABLEArea(Phone2_temp)
ALTER TABLE Phone ADD Phone_temp as left(Phone,7)CREATE INDEX phone_phone_temp_index ON phone (Phone_temp)
再:Select a.Phone, b.Area Into PhoneArea From Phone As a, Area as b Where a.Phone_temp between b.Phone1 And b.Phone2
http://expert.csdn.net/Expert/topic/2410/2410594.xml?temp=.1596491
然后将Area中的Phone1,Phone2转换为bigint,这样就是同等类型。re: 你同等类型就不存在转换的问题当然快了。之后如此:Select a.Area, p.Phone
From Area as a, Phone as p
Where p.Phone Between a.Phone1 And Phone2这样仅运行了几秒钟。但若将Phone1 和 Phone2 都乘10000(将号段转为11位,bigint)。Phone中的字段仍为原来的11位(bigint),这样就运行好多分钟(我没让他运行完)。re: 这又回到开始时你的问题上:Phone1 和 Phone2 都乘10000 你在select 时再乘的话,弊端在:没有用到表的索引,索引用不上! 你有10000条记录就好乘 200000次。你要:
ALTER TABLEArea ADD Phone1_temp as Phone1*1000
ALTER TABLEArea ADD Phone2_temp as Phone2*1000CREATE INDEX TABLEArea_Phone1_temp_index ON TABLEArea(Phone1_temp)
CREATE INDEX TABLEArea_Phone2_temp_index ON TABLEArea(Phone2_temp)
另可将号段字段,Phone存为bigint,减少存储空间和匹配检索.