paip.sql索引优化----join 代替子查询法

paip.sql索引优化----join 代替子查询法

作者Attilax ，  EMAIL:[email protected]
来源：attilax的专栏
地址：http://blog.csdn.net/attilax

select SQL_NO_CACHE  *,(
    SELECT
     LEFT (fecye, 1)
    FROM
     cyefenbyao   force index(i_hezi) 
    WHERE
     cyefenbyao.hezi = rit
  limit 1
   ) AS rit_cye
  FROM
   (
    select  
    max( gaopinzi.HEZI) hezi,
     LEFT (hezi, 1) AS lft,
     RIGHT (hezi, 1) AS rit
    FROM
     gaopinzi 
    WHERE
     LENGTH(hezi) = 6
 
      group by HEZI

limit 30000
   ) AS t2

要1S,改成join  0.5S..

select SQL_NO_CACHE 
t2.*  ,LEFT (fecye, 1) as t1
FROM
 (
  SELECT
   max(gaopinzi.HEZI) hezi,
   LEFT (hezi, 1) AS lft,
   RIGHT (hezi, 1) AS rit
  FROM
   gaopinzi
  WHERE
   LENGTH(hezi) = 6
  GROUP BY
   HEZI
  LIMIT 30000
 ) AS t2

left join  cyefenbyao
on cyefenbyao.hezi = t2.rit