Re: why aren't my PRIMARY KEYs being used?
I disagree with your LEFT JOIN/RIGHT JOIN results. SELECT activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc 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 ORDER BY activelayers.lid DESC; Since you RIGHT JOINed nrsrc and activenodes to your query, neither your 1st nor your 5th columns should contain any null values. So, the columns that could contain null values are the 2nd, 3rd, and 4th (as both activelayers and lrsrc are optional tables However, only your 2nd and 4th columns contain nulls. So, where are the optional (non-matching) rows for column 3? Where are those null values? There should be nulls there for everywhere you have a null in columns 2 and 4, right? ...OR... Are the tables activelayers, nrsrc, and activenodes participating in a Cartesian product and only lrsrc is actually optional? Which method of looking at this query is correct? Can you be certain that the same style query will respond with the same decisions about which tables are optional and which ones aren't (because of the mix of LEFT and RIGHT joins in the same query)? The only way to avoid this kind of order-of-operations dilemma is to use parentheses to group (nest) your joins so that they evaluate in the correct order. However, last time I checked, that is still on the TODO list to fix. http://dev.mysql.com/doc/mysql/en/TODO_sometime.html (see third from last) So to get back to the original issue. To recap: from http://lists.mysql.com/mysql/174702 snip Conceptually, here's what I'm trying to do: I've got a set of tasks to execute (frames). The frames which are ready to execute are in wait mode. These frames are associated with layers (in the table layers), and these layers have 0 or more layer resource requirements (in the table lrsrc). I also have a table of compute nodes (nodes). Each of these nodes has 0 or more node resources (in the table nrsrc). If a layer requires linux and perl resources, frames in that layer will only run on compute nodes which have linux and perl resources. snip What was the question are you trying to answer? I know it was something about matching layers and nodes but I am not perfectly clear on what results you wanted. I really do want to help and I don't want to argue over what I think is a bug (or an under-developed section) in the program. You may have received the results you wanted this time but I am not confident that this query is correct for the question you are asking nor am I confident that it will continue to return correct results in the future. Thanks for your patience, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Laszlo Thoth [EMAIL PROTECTED] wrote on 10/30/2004 04:21:15 AM: On Oct 29, 2004, at 6:26 AM, [EMAIL PROTECTED] wrote: 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. SELECT activelayers.id,activelayers.lid,activelayers.rsrcc,COUNT(lrsrc.rsrc=nr src.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; This actually didn't produce the same result. I'm doing a RIGHT JOIN rather than a LEFT or INNER JOIN to catch node resources (nrsrc) which do not match layer resources (lrsrc), or nodes with no layer resources at all. This example makes the difference a little clearer: SELECT activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc 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 ORDER BY activelayers.lid DESC; +---+--+---+--+--+ | name | lid | rsrcc | rsrc | rsrc | +---+--+---+--+--+ | node1 | NULL | 1 | NULL |1 | | node2 | NULL | 1 | NULL |2 | | node3 | NULL | 1 | NULL |1 | | node3 | NULL | 1 | NULL |2 | | node0 | NULL | 1 | NULL | NULL | | node1 |4 | 2 |1 |1 | | node2 |4 | 2 |2 |2 | | node3 |4 | 2 |1 |1 | | node3 |4 | 2 |2 |2 | | node0 | NULL | 2 | NULL | NULL | | node1 |3 | 2 |1 |1 | | node2 |3 | 2 |2 |2 | | node3 |3 | 2 |1 |1 | | node3 |3 | 2 |2 |2 | | node0 | NULL | 2 | NULL | NULL | | node1 | NULL | 1 | NULL |1 | | node2 |2 | 1 |2 |2
Re: why aren't my PRIMARY KEYs being used?
On Oct 29, 2004, at 6:26 AM, [EMAIL PROTECTED] wrote: 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. SELECT activelayers.id,activelayers.lid,activelayers.rsrcc,COUNT(lrsrc.rsrc=nr src.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; This actually didn't produce the same result. I'm doing a RIGHT JOIN rather than a LEFT or INNER JOIN to catch node resources (nrsrc) which do not match layer resources (lrsrc), or nodes with no layer resources at all. This example makes the difference a little clearer: SELECT activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc 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 ORDER BY activelayers.lid DESC; +---+--+---+--+--+ | name | lid | rsrcc | rsrc | rsrc | +---+--+---+--+--+ | node1 | NULL | 1 | NULL |1 | | node2 | NULL | 1 | NULL |2 | | node3 | NULL | 1 | NULL |1 | | node3 | NULL | 1 | NULL |2 | | node0 | NULL | 1 | NULL | NULL | | node1 |4 | 2 |1 |1 | | node2 |4 | 2 |2 |2 | | node3 |4 | 2 |1 |1 | | node3 |4 | 2 |2 |2 | | node0 | NULL | 2 | NULL | NULL | | node1 |3 | 2 |1 |1 | | node2 |3 | 2 |2 |2 | | node3 |3 | 2 |1 |1 | | node3 |3 | 2 |2 |2 | | node0 | NULL | 2 | NULL | NULL | | node1 | NULL | 1 | NULL |1 | | node2 |2 | 1 |2 |2 | | node3 | NULL | 1 | NULL |1 | | node3 |2 | 1 |2 |2 | | node0 | NULL | 1 | NULL | NULL | | node1 |1 | 1 |1 |1 | | node2 | NULL | 1 | NULL |2 | | node3 |1 | 1 |1 |1 | | node3 | NULL | 1 | NULL |2 | | node0 | NULL | 1 | NULL | NULL | | node1 | NULL | 0 | NULL |1 | | node2 | NULL | 0 | NULL |2 | | node3 | NULL | 0 | NULL |1 | | node3 | NULL | 0 | NULL |2 | | node0 | NULL | 0 | NULL | NULL | +---+--+---+--+--+ SELECT activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc 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 ORDER BY activelayers.lid DESC; +---+--+---+--+--+ | name | lid | rsrcc | rsrc | rsrc | +---+--+---+--+--+ | node1 |4 | 2 |1 |1 | | node3 |4 | 2 |1 |1 | | node2 |4 | 2 |2 |2 | | node3 |4 | 2 |2 |2 | | node1 |3 | 2 |1 |1 | | node3 |3 | 2 |1 |1 | | node2 |3 | 2 |2 |2 | | node3 |3 | 2 |2 |2 | | node2 |2 | 1 |2 |2 | | node3 |2 | 1 |2 |2 | | node1 |1 | 1 |1 |1 | | node3 |1 | 1 |1 |1 | +---+--+---+--+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why aren't my PRIMARY KEYs being used?
Hi. See: http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html http://dev.mysql.com/doc/mysql/en/EXPLAIN.html It is ok that MySQL doesn't use indexes when selecting indexed columns that are compared using the = operator. You can read how indexes works. Laszlo Thoth [EMAIL PROTECTED] wrote: 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 | ++-+--++---+-+-+-+--+-+ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why aren't my PRIMARY KEYs being used?
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]
why aren't my PRIMARY KEYs being used?
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]
