我有两张表table1,table2,table2中有一个字段要和table1中的两个字段进行比较,语句是:
select a.* from table1 a, table2 b where a.start <= b.colum and a.end>=b.column;
不管是建 table1 表上的start,end 组合索引 还是start ,end的单个索引,或者是加上强制索引:
select a.* from table1 a force index(index_name), table2 b where a.start <= b.colum and a.end>=b.column;查询速度很慢...对索引知道的还比较少,都不会走table1的索引,各位达人有什么好的建议没?

解决方案 »

  1.   

    b.column 加索引吧。 无法避免的其中一个表必须是全表扫描。
      

  2.   

    不会啊,start<end的,如果我在table1表上建一个无关的字段 type 默认值为1,然后在这个字段上建索引,关联两张表的时候加上条件: a.type = '1' ,这样确实能大大减少查询时间,时间还是不符合要求,而且type是所有字段都是1,总觉得很别扭
      

  3.   


    b.column已经加上了,而且也走索引...难道真没办法?查询范围数据都很慢么?
      

  4.   

    select a.* from table1 a, table2 b where a.start <= b.colum and a.end>=b.column;这种语句,没有办法,你必须选择其中一张表进行全表扫描,然后对另一张表进行索引上的优化。
    至于先哪个表进行全表扫描,要具体看你的表中数据的分布。
      

  5.   

    如果希望在这里讨论的话,则需要你贴出show index from table1;
    show index from table2;
    explain select a.* from table1 a, table2 b where a.start <= b.colum and a.end>=b.column;
      

  6.   

    a.start ,b.colum ,a.end
    都加上索引。
      

  7.   

    SHOW INDEX FROM table1;
    `Table`, `Non_unique`, `Key_name`,     `Seq_in_index`, `Column_name`, `Collation`, `Cardinality`, `Sub_part`, `Packed`, `Null`, `Index_type`, `Comment`, `Index_comment`
    'table1',   '1',      'IDX_table1_startend','1',          'startIp',      'A',           '197',       NULL,      NULL,   'YES',    'BTREE',      '',         ''
    'table1',    '1',     'IDX_table1_startend','2',          'endIp',        'A',           '197',       NULL,      NULL,    'YES',    'BTREE',      '',        ''SHOW INDEX FROM table2;
    `Table`, `Non_unique`, `Key_name`,     `Seq_in_index`, `Column_name`, `Collation`, `Cardinality`, `Sub_part`, `Packed`, `Null`, `Index_type`, `Comment`, `Index_comment`
    'table2',     '1',   'IDX_table2_intIp',      '1',        'intIp',        'A',           '143',       NULL,      NULL,   'YES',     'BTREE',      '',        ''
    `id`, `select_type`, `table`, `type`, `possible_keys`,          `key`,    `key_len`, `ref`, `rows`,  `Extra`
    '1',     'SIMPLE',     'b',  'index', 'IDX_table2_intIp', 'IDX_table2_intIp','5',      NULL,  '143',  'Using index'
    '1',     'SIMPLE',     'a',   'ALL',  'IDX_table1_startend',   NULL,         NULL,     NULL, '369307','Range checked for each record (index map: 0x1)'
    这个是6楼高手要求贴出来的,现在是table1里面有38W条记录并且以后基本不变,table2只有几百条,但是会时时增加.请高手们帮我看看.
      

  8.   

    分别创建 
    create index xxx1 on table1(start);
    create index xxx1 on table1(end);应该会提高一点儿。
    从本质上来说,数据不会快,应该你的查询条件过于宽松。 另外假设 table1中每条件记录都符合table2的这个a.start <= b.colum and a.end>=b.column条件,则等同于一个笛卡尔积。
      

  9.   

    另外没有看到你的 table2(column)的索引。
      

  10.   

    还有你的table1中不过才197条记录啊。
      

  11.   

    table1中有38W条记录,不清楚mysql是怎么预算的.table2(intIp) 索引:'IDX_table2_intIp'分别创建  
    create index xxx1 on table1(start);
    create index xxx1 on table1(end);
    这个我试过,没啥用,如果给加个条件start = 'xxx'这样就会走索引,速度就很快,不知道有没有一个万用字符啥的玩意,就像like '%'一样的玩意,这样就能控制走索引了..异想天开了有点....
      

  12.   

    建议把创建相关索引后的 show index 贴出来,还有你的EXPLAIN的结果。
    另外建议不要做任何修改,否则别人会对你的字段名,表名混淆反而引起错误理解。
      

  13.   

    这几个字段都是 ip转int unsigned ,为的是比较IP大小.table1中存放的是ip地址的范围和对应的地址,现在要找出table2中存放的IP对应的地址:
    table2.colum 就是table2.intIp
    下面是分别建 table1.startIp table1.endIp索引的相关信息:
    show index from table1:
    `Table`, `Non_unique`, `Key_name`, `Seq_in_index`, `Column_name`, `Collation`, `Cardinality`, `Sub_part`, `Packed`, `Null`, `Index_type`, `Comment`, `Index_comment`
    'table1',       '1', 'IDX_table1_start','1',          'startIp',       'A',        '199',         NULL,      NULL,    'YES',     'BTREE',     '',         ''
    'table1',       '1', 'IDX_table1_end','1',              'endIp',       'A',        '199',         NULL,      NULL,    'YES',     'BTREE',     '',         ''SHOW INDEX FROM table2;
    `Table`, `Non_unique`, `Key_name`, `Seq_in_index`, `Column_name`, `Collation`, `Cardinality`, `Sub_part`, `Packed`, `Null`, `Index_type`, `Comment`, `Index_comment`
    'table2', '1', 'IDX_table2_intIp', '1', 'intIp', 'A', '143', NULL, NULL, 'YES', 'BTREE', '', ''EXPLAIN SELECT a.* FROM table1 a, table2 b WHERE a.startIp <= b.intIp AND a.endIp >= b.intIp  ;`id`, `select_type`, `table`, `type`, `possible_keys`,                       `key`,       `key_len`, `ref`, `rows`, `Extra`
     '1',     'SIMPLE',     'b',   'index','IDX_table2_intIp',                'IDX_table2_intIp','5',     NULL,   '88', 'Using index'
     '1',     'SIMPLE',     'a',   'ALL',  'IDX_table1_start,IDX_table1_end',    NULL,           NULL,    NULL,  '379392','Range checked for each record (index map: 0x3)' ;不知道各位对两个表中的IP比较有没有建议...
      

  14.   

    `Table` `Non_unique` `Key_name` `Seq_in_index` `Column_name` `Collation` `Cardinality` `Sub_part` `Packed` `Null`
    'table1' 1' IDX_table1_start' '1' startIp' A' 199' NULL NULL YES'
    'table1' 1' IDX_table1_end' '1' endIp' A' 199' NULL NULL YES'
    这个比较奇怪,表中有记录380,000条 ,但这380,000条中的startIp基本上都完全相同,其中只有199个不同值。如果情况真是这样。建议SQL语句试试:
    select * from table1 a
    where exists (select 1 from table2 where intIp between a.startIp and b.intIp)
    不过这个语句逻辑上与你的语句并不相等,它不会列重复匹配的记录。