I've created two temporary tables:

CREATE TEMPORARY TABLE `activenodes` (
  `id` smallint(6) unsigned NOT NULL default '0',
  `name` varchar(50) NOT NULL default '',
  `rsrcc` bigint(21) NOT NULL default '0',
  PRIMARY KEY  (`id`)
);

CREATE TEMPORARY TABLE `activelayers` (
  `id` int(10) unsigned NOT NULL default '0',
  `lid` tinyint(3) unsigned NOT NULL default '0',
  `rsrcc` bigint(21) NOT NULL default '0',
  PRIMARY KEY  (`id`,`lid`)
);

I've also got two non-temporary tables:

CREATE TABLE `nrsrc` (
  `id` smallint(6) unsigned NOT NULL default '0',
  `rsrc` smallint(6) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`,`rsrc`),
  KEY `rsrc` (`rsrc`)
);

CREATE TABLE `lrsrc` (
  `id` int(10) unsigned NOT NULL default '0',
  `lid` tinyint(3) unsigned NOT NULL default '0',
  `rsrc` smallint(6) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`,`lid`,`rsrc`),
  KEY `rsrc` (`rsrc`)
);


I'm attempting to perform the following join:

SELECT
activelayers.id,activelayers.lid,activelayers.rsrcc,COUNT(lrsrc.rsrc=nrsrc.rsrc)
as matchcount,activenodes.name,activenodes.rsrcc
FROM activelayers
LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND lrsrc.lid=activelayers.lid
RIGHT JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc
RIGHT JOIN activenodes ON nrsrc.id=activenodes.id
GROUP BY activelayers.id,activelayers.lid,activenodes.id
HAVING matchcount=activelayers.rsrcc
ORDER BY activelayers.lid DESC;

My EXPLAIN tells me that I will not be using either of the primary keys in my
temporary tables:

+----+-------------+--------------+--------+---------------+---------+---------+---------------------------------------------------------------------+------+---------------------------------+
| id | select_type | table        | type   | possible_keys | key     | key_len |
ref                                                                 | rows |
Extra                           |
+----+-------------+--------------+--------+---------------+---------+---------+---------------------------------------------------------------------+------+---------------------------------+
|  1 | SIMPLE      | activenodes  | ALL    | NULL          | NULL    |    NULL |
NULL                                                                |    3 |
Using temporary; Using filesort |
|  1 | SIMPLE      | nrsrc        | ref    | PRIMARY       | PRIMARY |       2 |
sherman.activenodes.id                                              |    2 |
Using index                     |
|  1 | SIMPLE      | activelayers | ALL    | NULL          | NULL    |    NULL |
NULL                                                                |    6 |    
                            |
|  1 | SIMPLE      | lrsrc        | eq_ref | PRIMARY,rsrc  | PRIMARY |       7 |
sherman.activelayers.id,sherman.activelayers.lid,sherman.nrsrc.rsrc |    1 |
Using index                     |
+----+-------------+--------------+--------+---------------+---------+---------+---------------------------------------------------------------------+------+---------------------------------+

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to