优化MySQL中的多连接

Question

select c.cat_id,c.cat_name as cat_name, 
 c.cat_desc, c.cat_image, mi.filename, 
 l.link_id, l.user_id, l.address,l.city, 
 l.country,l.link_created,l.link_desc, 
 l.email,l.fax,l.link_hits, l.link_modified,
 l.link_name,l.postcode, l.price,l.link_rating, 
 l.state,l.telephone,l.link_votes,
 l.website, l.link_id, l.link_visited, cf.value
from j25_mt_cats as c,
j25_mt_links as l 
LEFT OUTER JOIN j25_mt_cfvalues AS cf ON (cf.link_id = l.link_id),
j25_mt_images AS mi,
j25_mt_cl as cl
UNION ALL
select c.cat_id,c.cat_name as cat_name, 
 c.cat_desc, c.cat_image, mi.filename, 
 l.link_id, l.user_id, l.address,l.city, 
 l.country,l.link_created,l.link_desc, 
 l.email,l.fax,l.link_hits, l.link_modified,
 l.link_name,l.postcode, l.price,l.link_rating, 
 l.state,l.telephone,l.link_votes,
 l.website, l.link_id, l.link_visited, cf.value
FROM j25_mt_cats as c,
j25_mt_links as l
RIGHT OUTER JOIN j25_mt_cfvalues AS cf ON cf.link_id = l.link_id,
j25_mt_images AS mi,
j25_mt_cl as cl
where cf.cf_id = 40 and cl.link_id = l.link_id 
 AND mi.link_id = l.link_id AND mi.ordering < 2  
 AND c.cat_id = cl.cat_id and c.cat_published = 1 
 AND c.cat_approved = 1 and l.link_published = 1 and l.link_approved = 1
 AND cf.link_id IS NULL;

该查询占用了tmp目录中的3GB+并最终超时。我这里少了点什么，怎样才能提高效率？我在这里的目标只是添加到现有查询中，以便从额外的表(j25_mt_cfvalues)中获取值。

解释:

+----+--------------+------------+-------+---------------+---------+---------+--------------------------+------+------------------+
| id | select_type  | table      | type  | possible_keys | key     | key_len | ref                      | rows | Extra            |
+----+--------------+------------+-------+---------------+---------+---------+--------------------------+------+------------------+
|  1 | PRIMARY      | mi         | ALL   | NULL          | NULL    | NULL    | NULL                     |  165 |                  |
|  1 | PRIMARY      | c          | ALL   | NULL          | NULL    | NULL    | NULL                     |  301 |                  |
|  1 | PRIMARY      | l          | ALL   | NULL          | NULL    | NULL    | NULL                     | 2139 |                  |
|  1 | PRIMARY      | cf         | ref   | link_id       | link_id | 4       | db_table.l.link_id |    2 |                  |
|  1 | PRIMARY      | cl         | index | NULL          | PRIMARY | 4       | NULL                     | 2742 | Using index      |
|  2 | UNION        | NULL       | NULL  | NULL          | NULL    | NULL    | NULL                     | NULL | Impossible WHERE |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL                     | NULL |                  |
+----+--------------+------------+-------+---------------+---------+---------+--------------------------+------+------------------+

j25_mt_cats模式：

Answer 1

问题是，您的第一个SQL查询没有任何WHERE条件，并在您正在处理的每个表中造成全局笛卡尔。只有在第二个查询中才应用WHERE子句。

也就是说，CF表有一个左联接和右联接，但不能同时有CF=40和CF为NULL，所以我简化为只有ID和40的左联接...因此，如果CF表中有一条记录，它只显示它的值是40...任何其他值都将被忽略。

也就是说，您的查询可以简化为一个查询。我还更改了连接语法，而不是哪里，这样您和其他人就可以看到与表的关系，而不是猜测。

select 
      (all your fields)
   from 
      j25_mt_cats as c
         JOIN j25_mt_cl as cl
            ON c.cat_id = cl.cat_id 
            JOIN j25_mt_links as l 
               ON cl.link_id = l.link_id 
               AND l.link_published = 1 
               AND l.link_approved = 1
               JOIN j25_mt_images AS mi
                 ON l.link_id = mi.link_id 
                AND mi.ordering < 2  
                LEFT OUTER JOIN j25_mt_cfvalues AS cf 
                   ON l.link_id = cf.link_id
                   AND cf.cf_id = 40
   where
          c.cat_published = 1 
      AND c.cat_approved = 1 
   ORDER BY 
      RAND() DESC;

j25_mt_cats should have an index on (cat_published, cat_approved)
j25_mt_cl on (cat_id)
j25_mt_links on (link_id, link_published, link_approved)
j25_mt_images on (link_id, ordering)
j25_mt_cfvalues on (link_id, cf_id)