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]
