为什么查询时索引没用上??

Question

table a (chr varchar(50),start int) 上有索引idx_chr和idx_start
table b(chr varchar(50),pos int) 上有索引idx_chr和idx_pos
索引类型都是btreeexpain一下关联两个表:
explain extended select b.chr,b.pos,a.start from patags b,gff_noigt a where a.chr=b.chr and b.pos<a.start
结果是:
table type possible_keys key key_len ref rows Extra
a ALL idx_start,idx_chr 317787
b ref idx_pos,idx_chr idx_chr 52 arabpat_raw.a.chr 32687 Using where现在的问题是下面的语句根本就不用b的idx_pos索引(b的key列只有idx_chr),怎么才可以用上呢?
我的a,b表里都有几十万的数据,如果pos列的数据都是不同的. 而chr基数比较小,只有7种值.谢谢

Answer 1

创建复合索引
idx_chr_start
idx_chr_pos否则你的这个  a.chr=b.chr and b.pos <a.start 无法使用索引。1. 你的 chr 的 Cardinality 过小。
2. b.pos <a.start 两边都是字段，非常量，只能对逐个记录进行比较建立复合索引后，会略有提高。

Answer 2

1. chr基数这个没办法,数据就是那样的.
2. 我建了b.idx(chr,pos)和a.idx(chr,start)还是不行. explain后key全是NULL,说明索引还是没用上.或者说,有什么办法能够使关联两个表的速度变快啊?　就算表只有不到10万条数据,这样关联也要很久..1个小时还没出来,真奇怪.是不是哪出问题了.

Answer 3

2. 我建了b.idx(chr,pos)和a.idx(chr,start)还是不行. explain后key全是NULL,说明索引还是没用上. ~~~ 这个修正一下:
explain后b.idx(chr,pos)还是没有出现在key中, a.idx(chr,start)有在key中. 但关键是pos的索引啊.

Answer 4

show index from a;
show index from b;贴出来看一下。

Answer 5

谢谢 ACMAIN_CHM,我现在结帖. 虽然我用另外的方式关联这两个表了.刚重新试了下,居然就可以用上索引了.
现在明白了:原来where字段有多少个,就得用多少个字段的复合索引的.这个是表a的:
show index FROM gff_noigt_5_plus;
'gff_noigt_5_plus', 1, 'Index_chrstart', 1, 'chr', 'A', 2, , '', 'YES', 'BTREE', ''
'gff_noigt_5_plus', 1, 'Index_chrstart', 2, 'start', 'A', 37722, , '', 'YES', 'BTREE', ''表b:
show index FROM patags_5_plus;
'patags_5_plus', 1, 'Index_chrpos', 1, 'chr', 'A', 1, , '', '', 'BTREE', ''
'patags_5_plus', 1, 'Index_chrpos', 2, 'pos', 'A', 30416, , '', '', 'BTREE', ''只有表a的索引用上了:
explain extended select b.chr,b.polyA_site_in_genome,a.start from patags_5_plus b,gff_noigt_5_plus a where a.chr=b.chr and b.pos <a.start1, 'SIMPLE', 'b', 'index', 'Index_chrpos', 'Index_chrpos', '56', '', 30416, 'Using index'
1, 'SIMPLE', 'a', 'ref', 'Index_chrstart', 'Index_chrstart', '53', 'arabpat_anti.b.chr', 18775, 'Using where; Using index'