请问一个SQL语句的优化问题 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

请问一个SQL语句的优化问题

请问一个SQL语句的优化问题# Database - 数据库

k*s2004-01-11 08:01

1 楼

q is a table with only one field, snip (int), 180,000 records
ip2country is a table contain ip ranges for each country,
there are about 140,000 records.
我在做如下查询时，非常慢
select q.snip, p.country from q, ip2country as p
where q.snip >= p.ip_range_begin && q.snip <= p.ip_range_end
请问有什么方法可以进行优化？
多谢了

b*l2004-01-11 08:01

2 楼

where q.snip between p.ip_range_begin and p.ip_range_end.
you should add a join condition for these two tables

【在 k***s 的大作中提到】

: q is a table with only one field, snip (int), 180,000 records
: ip2country is a table contain ip ranges for each country,
: there are about 140,000 records.
: 我在做如下查询时，非常慢
: select q.snip, p.country from q, ip2country as p
: where q.snip >= p.ip_range_begin && q.snip <= p.ip_range_end
: 请问有什么方法可以进行优化？
: 多谢了

k*s2004-01-11 08:01

3 楼

I'm a newbie, could you give me a example how to do it?
Thanks,

【在 b******l 的大作中提到】

: where q.snip between p.ip_range_begin and p.ip_range_end.
: you should add a join condition for these two tables

w*n2004-01-11 08:01

4 楼

Add index to both tables, which should improve the performance.

【在 k***s 的大作中提到】

: I'm a newbie, could you give me a example how to do it?
: Thanks,

h*i2004-01-11 08:01

5 楼

假如你没有基本的两个TABLE的JOIN，就好比在做Cartesian product，返回的结果
将会是巨大的数目。自然会很慢。。。
SELECT a.id, b.grade
from student a,
grade b
where a.id = b.id
and a.class_date between b.from_date and b.to_date

【在 k***s 的大作中提到】

: I'm a newbie, could you give me a example how to do it?
: Thanks,