I think it may be because of your mixed left and right joins. There are several bugs listed that show that the optimizer mishandles certain combinations of left and right joins.
May I suggest that you re-arrange your query to include only LEFT and INNER joins (at least until the bugs are worked out). The following query should return the same set of records (all records from activelayers with optional records from lrsrc, nrsrc, and activenodes but only if the lrsrc records match with those in activelayers, the nrsrc rows match with rows from lrsrc, and the activenodes rows match with those from nrsrc) 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 INNER JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc INNER JOIN activenodes ON nrsrc.id=activenodes.id GROUP BY activelayers.id,activelayers.lid,activenodes.id HAVING matchcount=activelayers.rsrcc ORDER BY activelayers.lid DESC; Usually what people want when they do a LEFT join and a RIGHT join in the same query is something known in other products as a FULL OUTER join. You can duplicate that behavior by running two queries (one directed from the "left", the other from the "right") and unioning their results together. Let me know if that was what you were after and I will help you to transform this into a FULL OUTER join. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Laszlo Thoth <[EMAIL PROTECTED]> wrote on 10/28/2004 08:15:20 PM: > 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] >
