how to optimize this join query?

Here is the my previous experiences about optimizing sql join:

in MySQL, to have fast join, usually I will avoid type 'All'. Basically, join will be faster if they have index fields in the table. Even you use WHERE clause etc.. to create smaller subsets, if two subsets have no index for join, that query must be very much slower than a full table join with indexes and then apply WHERE clause etc.. 开发者_运维知识库to get the final result set.

However, how to optimize this kind of queries?

I have to select out subsets for join. In that case, those derived subsets won't have index for join.

Here is the specific problem I have:

I have a table called Contract, and it has the following fields (to make it simple, actually more fields):

SN, type, date, flag

SN and type are its primary key, flag field can be 0/1/2

Here is the join query I created:

SELECT f0_crt.SN, f0_crt.f0_Info, f1_Info
FROM 
  (SELECT a.SN, GROUP_CONCAT(a.type, '-', a.date SEPARATOR '*') as f0_Info
   FROM Contract a
   WHERE a.flag=0 
   GROUP BY SN
   ORDER BY SN ) AS f0_crt
  LEFT JOIN 
  (SELECT b.SN, GROUP_CONCAT(b.type, '-', b.date SEPARATOR '*') as f1_Info
    FROM Contract b
    WHERE b.flag=1 
    GROUP BY SN
    ORDER BY SN ) AS f1_crt
    ON f0_crt.SN=f1_crt.SN

basically, I need to result set to be like:

SN                  f0_Info                              f1_Info
abc        COMB-20100911*CDMA-20090701        FFIV-20100911*SPRT-20090701

Here is the EXPLAIN result:

id  select_type   table     type    possible_keys   key key_len ref rows    Extra
---------------------------------------------------------------------------------------------
1   PRIMARY      derived2    ALL           (NULL)    (NULL) (NULL)    (NULL)    15052    
1   PRIMARY      derived3    ALL           (NULL)    (NULL) (NULL)    (NULL)    29407    
3   DERIVED         b      index    seq_num_index   PRIMARY    184  (NULL)  81982    
2   DERIVED         a       index   seq_num_index   PRIMARY    184  (NULL)  81982    

that's the actually EXPLAIN result, the key reason of the slowness I think is: the two ALL join. However, I dont know how to avoid it here? Is there a way to get the group format I need and then do the join with SN so that the SN index etc.. can be used?

Thanks!

What if you modify your query to look something like this?

SELECT a.SN, GROUP_CONCAT(a.type, '-', a.date SEPARATOR '*') as f0_Info,
       GROUP_CONCAT(b.type, '-', b.date SEPARATOR '*') as f1_Info
  FROM Contract a
  LEFT JOIN Contract b
      on a.SN = b.SN and b.flag = 1
  WHERE a.flag=0 
  GROUP BY a.SN
  ORDER BY a.SN