-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I am trying to figure out why a query sometimes doesn't work, and so I decided to use explain to help me. We are using myslq 4.1.8 at the moment, btw.
The only difference between the two queries is I change skywise to rcooksey. mysql> explain select * FROM items i, nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON a.role=r.code LEFT OUTER JOIN nams.regterms AS t ON (na.badge=t.badge) LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid) WHERE ((a.enddate IS NULL AND r.lmsvalid='Y') OR (t.termcode=200508 and t.registered='Y')) AND i.rid=1999 AND n.netid='skywise' AND (i.status='A' OR c.badge IS NULL); +----+-------------+-------+--------+-------------------------+------------+---------+--------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-------------------------+------------+---------+--------------+------+-------------+ | 1 | SIMPLE | i | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | n | const | PRIMARY,netid_badge_ndx | PRIMARY | 12 | const | 1 | | | 1 | SIMPLE | na | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | a | ref | badge_ndx,role_ndx | badge_ndx | 4 | const | 1 | | | 1 | SIMPLE | r | eq_ref | PRIMARY | PRIMARY | 2 | nams.a.role | 1 | | | 1 | SIMPLE | t | ref | badge_ndx | badge_ndx | 4 | nams.a.badge | 3 | Using where | | 1 | SIMPLE | c | ref | curuse_ndx | curuse_ndx | 4 | const | 1 | Using where | +----+-------------+-------+--------+-------------------------+------------+---------+--------------+------+-------------+ 7 rows in set (0.00 sec) mysql> explain select * FROM items i, nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON a.role=r.code LEFT OUTER JOIN nams.regterms AS t ON (na.badge=t.badge) LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid) WHERE ((a.enddate IS NULL AND r.lmsvalid='Y') OR (t.termcode=200508 and t.registered='Y')) AND i.rid=1999 AND n.netid='cooksey' AND (i.status='A' OR c.badge IS NULL); +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.01 sec) - -- "Love is mutual self-giving that ends in self-recovery." Fulton Sheen James Black [EMAIL PROTECTED] -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDaRv2ikQgpVn8xrARAswuAJ9+bcmZ0i17hD2AJatCo4pAi1yBggCgjTv8 AWdskslAfIAU8f68LJNYKKc= =d+7U -----END PGP SIGNATURE----- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
