我需要提高查询的

Question

它工作得很好了10000条记录，但对于230万个记录它不工作，我怎么也得处理这种类型的查询，以提高性能请帮助我更新鲜到这个MySQL

select 
    r1.countrycode, 
    r1.code, 
    r1.rate, 
    f.cdr_id, 
    f.cld, 
    f.direction, 
    from 
    rates1 r1, 
    calls f 
    where(r1.code,f.cld) in 
    (select 
    max(r1.code), 
    f.cld 
    from rates1 r1, calls f 
    where (f.cld regexp concat('^',r1.code,'[0-9]+$')) 
    group by f.cld 

性能

）;

Sub query returns the Unique values for code and cld and main query use the code and cld in where 
condition and result the all the values 

calls table data 
cdr_id  cld  direction duration 
1  1985484555 incoming 59 
2  8475858585 outgoing 456 
3  1895858888 outgoing 555 
4  1548458455 incomimg 895 
5  548585665 incoming 585 
6  1985484555 outgoing 585 

rates1 table data 
countryocde code  rate 
040   19854 0.35 
080   198  0.356 
578   847  0.25 
458   1548 0.50 
555   1548 0.75 

喜的朋友，我写这篇文章的查询，以挑选准则，为2代表其做工精细，我少的记录数的唯一值CLD colums，它不excuting了超过100万记录其执行长期以来给出任何结果，请帮助提前致谢。

Answer 1

开始得到国家代码：

select c.*, 
     (select countrycode 
     from rates1 r1 
     where c.cld like concat(r1.code, '%') 
     order by length(r1.code) desc 
     limit 1 
    ) as countrycode 
from calls c; 

然后，加入汇率信息回：

select * 
from (select c.*, 
      (select r1.code 
       from rates1 r1 
       where c.cld like concat(r1.code, '%') 
       order by length(r1.code) desc 
      limit 1 
      ) as r1code 
     from calls c 
    ) c join 
    rates1 r 
    on c.r1code = r.code 

这将仍然表现不佳，但它会好于二级的笛卡尔积。

你可以做这个用的rates1(code)索引和过程是怎样的执行速度更快：

select c.*, coalesce(r5.countrycode, r4.countrycode, r3.countrycode, r2.countrycode, r1.countrycode) as countrycode 
from calls c left join 
    rates1 r1 
    on r1.code = left(c.code, 1) left join 
    rates1 r2 
    on r2.code = left(c.code, 2) left join 
    rates1 r3 
    on r3.code = left(c.code, 3) left join 
    rates1 r4 
    on r4.code = left(c.code, 4) left join 
    rates1 r5 
    on r5.code = left(c.code, 5); 

你需要足够的加入对于可能的代码长度最长。