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]
