Optimize multiple JOINs in MySQL

Question

I have this MySQL which should be correct syntax:

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;

The query eats up 3GB+ in the tmp directory and ends up timing out. I'm missing something here, how can I increase the efficiency? My goal here was just adding onto an existing query to grab a value from an additional table (j25_mt_cfvalues).

explain:

+----+--------------+------------+-------+---------------+---------+---------+--------------------------+------+------------------+
| 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 schema:

CREATE TABLE j25_mt_cats ( cat_id int(11) NOT NULL auto_increment, cat_name varchar(255) NOT NULL, alias varchar(255) NOT NULL, title varchar(255) NOT NULL, cat_desc text NOT NULL, cat_parent int(11) NOT NULL default '0', cat_links int(11) NOT NULL default '0', cat_cats int(11) NOT NULL default '0', cat_featured tinyint(4) NOT NULL default '0', cat_image varchar(255) NOT NULL, cat_published tinyint(4) NOT NULL default '0', cat_created datetime NOT NULL default '0000-00-00 00:00:00', cat_approved tinyint(4) NOT NULL default '0', cat_template varchar(255) NOT NULL default '', cat_usemainindex tinyint(4) NOT NULL default '0', cat_allow_submission tinyint(4) NOT NULL default '1', cat_show_listings tinyint(3) unsigned NOT NULL default '1', metakey text NOT NULL, metadesc text NOT NULL, ordering int(11) NOT NULL default '0', lft int(11) NOT NULL default '0', rgt int(11) NOT NULL default '0', PRIMARY KEY (cat_id), KEY cat_id (cat_id,cat_published,cat_approved), KEY cat_parent (cat_parent,cat_published,cat_approved,cat_cats,cat_links), KEY dtree (cat_published,cat_approved), KEY lft_rgt (lft,rgt), KEY func_getPathWay (lft,rgt,cat_id,cat_parent), KEY alias (alias) ) ENGINE=MyISAM AUTO_INCREMENT=3851 DEFAULT CHARSET=utf8 |

j25_mt_links schema:

CREATE TABLE j25_mt_links ( link_id int(11) NOT NULL auto_increment, link_name varchar(255) NOT NULL, alias varchar(255) NOT NULL, link_desc mediumtext NOT NULL, user_id int(11) NOT NULL default '0', link_hits int(11) NOT NULL default '0', link_votes int(11) NOT NULL default '0', link_rating decimal(7,6) unsigned NOT NULL default '0.000000', link_featured smallint(6) NOT NULL default '0', link_published tinyint(4) NOT NULL default '0', link_approved int(4) NOT NULL default '0', link_template varchar(255) NOT NULL, attribs text NOT NULL, metakey text NOT NULL, metadesc text NOT NULL, internal_notes text NOT NULL, ordering int(11) NOT NULL default '0', link_created datetime NOT NULL default '0000-00-00 00:00:00', publish_up datetime NOT NULL default '0000-00-00 00:00:00', publish_down datetime NOT NULL default '0000-00-00 00:00:00', link_modified datetime NOT NULL default '0000-00-00 00:00:00', link_visited int(11) NOT NULL default '0', address varchar(255) NOT NULL, city varchar(255) NOT NULL, state varchar(255) NOT NULL, country varchar(255) NOT NULL, postcode varchar(255) NOT NULL, telephone varchar(255) NOT NULL, fax varchar(255) NOT NULL, email varchar(255) NOT NULL, website varchar(255) NOT NULL, price double(9,2) NOT NULL default '0.00', lat float(10,6) NOT NULL COMMENT 'Latitude', lng float(10,6) NOT NULL COMMENT 'Longitude', zoom tinyint(3) unsigned NOT NULL COMMENT 'Map''s zoom level', PRIMARY KEY (link_id), KEY link_rating (link_rating), KEY link_votes (link_votes), KEY link_name (link_name), KEY publishing (link_published,link_approved,publish_up,publish_down), KEY count_listfeatured (link_published,link_approved,link_featured,publish_up,publish_down,link_id), KEY count_viewowner (link_published,link_approved,user_id,publish_up,publish_down), KEY mylisting (user_id,link_id), FULLTEXT KEY link_name_desc (link_name,link_desc) ) ENGINE=MyISAM AUTO_INCREMENT=3229 DEFAULT CHARSET=utf8 |

j25_mt_cfvalues schema:

CREATE TABLE j25_mt_cfvalues ( id int(11) NOT NULL auto_increment, cf_id int(11) NOT NULL, link_id int(11) NOT NULL, value mediumtext NOT NULL, attachment int(10) unsigned NOT NULL default '0', counter int(11) NOT NULL default '0', PRIMARY KEY (id), KEY cf_id (cf_id,link_id), KEY link_id (link_id), KEY value (value(8)) ) ENGINE=MyISAM AUTO_INCREMENT=20876 DEFAULT CHARSET=utf8 |

Answer 1

The problem is that your first SQL query does not have any WHERE criteria and is causing a global cartesian across EVERY table your are working. Only in the second query does the WHERE clause get applied.

That said, you had a left and right join for the CF table, but you can't have both a cf=40 and cf IS NULL, so I simplified to just a left join on the ID AND 40... so if there IS a record in the CF table it only shows if it's value is 40... any other value would be ignored..

that said, your query can be simplified down to a single query. I also changed to JOIN syntax instead of WHERE so you and others can see the relation to the tables vs guessing.

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;

TO help optimize the query, your

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)