关于mysql高效的大表关联查找问题

[xrzs1986]

1、表 a 是一列数字, 50 W 的记录量 ：

2
5
12
14
17

2、表 b 有 5列，1200W 的记录量，字段分别是 表 a 的start范围、表 a 的end范围, col3, col4, col5

1 2 c13 c14 c15
3 3 c23 c24 c25
7 9 c33 c34 c35
13 20 c43 c44 c45

3、需求：a中的元素如果在b中的第一列与第二列之间，就算匹配成功，然后输出后面的3、4、5列到文件 c
例如 运行程序后，c中的内容应该是：

c13 c14 c15
c43 c44 c45
c43 c44 c45

4、环境：suse linux mysql

5、自己写的 select很久才出来一个结果，看大家有没有更高效简洁的办法，谢谢！

[wwwwb]

在B的col1、col2上建立复合索引
select b.* from b inner join a on a.col1 between b.col1 and col2

[wwwwb]

在B的col1、col2上建立复合索引
select b.* from b inner join a on a.col1 between b.col1 and col2

[wwwwb]

在B的col1、col2上建立复合索引
select b.* from b inner join a on a.col1 between b.col1 and col2

[rucypli]

在B的col1、col2上建立复合索引
select b.col3,b.col4,b.col5 from a straight_join b on a.col1 between b.col1 and col2

[ACMAIN_CHM]

复合索引并不任何实际意义。 直接建个 B的col1 的索引就行了。如果要提高一些效率则应该创建两个单独的索引 col1 , col2

如果你的数据分布平均，则有效的索引是 表 a 的这一列的索引。

[xrzs1986]

联合索引没有明显效果，不过还是非常感谢提供思路！

mysql> select b.country, b.province, b.city from ip_region_myisam as b inner join ip_data as a on a.ip between b.start_ip and b.end_ip limit 10 ;
+---------+----------+------------+
| country | province | city       |
+---------+----------+------------+
| 中国    | 内蒙古   | 乌兰察布市 | 
| 中国    | 内蒙古   | 乌兰察布市 | 
| 中国    | 内蒙古   | 乌兰察布市 | 
| 中国    | 内蒙古   | 乌兰察布市 | 
| 中国    | 内蒙古   | 包头市     | 
| 中国    | 内蒙古   | 锡林郭勒盟 | 
| 中国    | 内蒙古   | 锡林郭勒盟 | 
| 中国    | 内蒙古   | 锡林郭勒盟 | 
| 中国    | 内蒙古   | 锡林郭勒盟 | 
| 中国    | 内蒙古   | 锡林郭勒盟 | 
+---------+----------+------------+
10 rows in set (1 min 53.50 sec)

mysql> 

[Quote=引用 3 楼 wwwwb 的回复:]

在B的col1、col2上建立复合索引
select b.* from b inner join a on a.col1 between b.col1 and col2
[/Quote]

[xrzs1986]

结果同楼上，非常感谢！
[Quote=引用 4 楼 rucypli 的回复:]

在B的col1、col2上建立复合索引
select b.col3,b.col4,b.col5 from a straight_join b on a.col1 between b.col1 and col2
[/Quote]

[xrzs1986]

非常感谢提供思路，无论a或者b建立单个索引或者联合索引，效果都不明显，具体结果请见6楼，谢谢！
[Quote=引用 5 楼 acmain_chm 的回复:]

复合索引并不任何实际意义。 直接建个 B的col1 的索引就行了。如果要提高一些效率则应该创建两个单独的索引 col1 , col2

如果你的数据分布平均，则有效的索引是 表 a 的这一列的索引。
[/Quote]

[liuxinran819]

因为需要对b表进行全表扫描，所以在b表上不需要建索引；而a表上必须建立索引。
之前没看LZ在六楼的贴出结果集，以为楼主是想筛选b表，删选后b表的数据小于1200万：
select b.country, b.province, b.city from ip_region_myisam as b
where exists (select a.ip between b.start_ip and b.end_ip limit 1)
原来楼主想要join后有重复结果的结果集~

[maquan]

[Quote=引用 7 楼 xrzs1986 的回复:]
结果同楼上，非常感谢！
引用 4 楼 rucypli 的回复:
在B的col1、col2上建立复合索引
select b.col3,b.col4,b.col5 from a straight_join b on a.col1 between b.col1 and col2
[/Quote]
这个也不行吗？B上不建任何索引，A建索引！

如果这个还不行，估计在语法层面就没什么好优化的了，只能看看 mysqld 的配置参数了。

[xrzs1986]

恩，也问过同事，这种情况，只能自己写程序处理了，数据库这样没法搞了。
[Quote=引用 10 楼 maquan 的回复:]
引用 7 楼 xrzs1986 的回复:
结果同楼上，非常感谢！
引用 4 楼 rucypli 的回复:
在B的col1、col2上建立复合索引
select b.col3,b.col4,b.col5 from a straight_join b on a.col1 between b.col1 and col2

这个也不行吗？B上不建任何索引，A建索引！

如果这个还不行，估计……
[/Quote]