Hi, I'm not sure I understand indexes properly.
In this EXPLAIN, I expected the Cls (Classes) table to be of type ref with
the key being ObjectID. I'm joining both identically as far as I can tell,
on one of the columns in the primary key, which is set to a key itself, but
Cls is joining on ALL. Why is it doing that?
Has it got something to do with the way my query is worded?
Thanks in advance,
Chris
=EXPLAIN===================================================================
+---------+--------+------------------+----------+---------+-------------------------------------+------+---------------------------
-------------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra
|
+---------+--------+------------------+----------+---------+-------------------------------------+------+---------------------------
-------------------+
| NPCSpl | index | PRIMARY | PRIMARY | 10 | NULL
| 6034 | Using index; Using
temporary; Using filesort |
| ObjNPCs | eq_ref | PRIMARY | PRIMARY | 8 |
const,NPCSpl.FileID,NPCSpl.ObjectID | 1 | Using where; Using index
|
| NPCs | eq_ref | PRIMARY,ObjectID | PRIMARY | 6 |
ObjNPCs.FileID,ObjNPCs.ObjectID | 1 |
|
| Spl | ref | PRIMARY,ObjectID | ObjectID | 4 | NPCSpl.ObjectID_Spell
| 15 |
|
| ObjSpl | eq_ref | PRIMARY | PRIMARY | 8 |
const,Spl.FileID,Spl.ObjectID | 1 | Using where; Using index
|
| Cls | ALL | PRIMARY,ObjectID | NULL | NULL | NULL
| 84 | Using where
|
| ObjCls | eq_ref | PRIMARY | PRIMARY | 8 |
const,Cls.FileID,Cls.ObjectID | 1 | Using where; Using index
|
+---------+--------+------------------+----------+---------+-------------------------------------+------+---------------------------
-------------------+
=QUERY=====================================================================
SELECT
NPCs.FileID,
NPCs.ObjectID,
NPCs.Name,
Spl.FileID as FileID_Spell,
Spl.ObjectID as ObjectID_Spell,
Spl.Name as Name_Spell
FROM NPCs
JOIN GroupedObjects ObjNPCs
ON
(
1=ObjNPCs.GroupID
AND
NPCs.FileID=ObjNPCs.FileID
AND
NPCs.ObjectID=ObjNPCs.ObjectID
)
JOIN NPCSpells NPCSpl
ON
(
ObjNPCs.FileID=NPCSpl.FileID
AND
ObjNPCs.ObjectID=NPCSpl.ObjectID
)
JOIN Spells Spl
ON
(
Spl.ObjectID=NPCSpl.ObjectID_Spell
)
INNER JOIN GroupedObjects ObjSpl
ON
(
1=ObjSpl.GroupID
AND
Spl.FileID=ObjSpl.FileID
AND
Spl.ObjectID=ObjSpl.ObjectID
)
JOIN Classes Cls
ON
(
Cls.ObjectID=NPCs.ObjectID_Class
)
INNER JOIN GroupedObjects ObjCls
ON
(
1=ObjCls.GroupID
AND
Cls.FileID=ObjCls.FileID
AND
Cls.ObjectID=ObjCls.ObjectID
)
WHERE
2048 & NPCs.Services
OR
2048 & Cls.Services
ORDER BY
NPCs.ObjectID;
=TABLES====================================================================
CREATE TABLE `Spells` (
`FileID` smallint(5) unsigned NOT NULL default '0',
`ObjectID` int(10) unsigned NOT NULL default '0',
`Name` char(32) default NULL,
`SpellTypeID` tinyint(4) NOT NULL default '0',
`Cost` int(11) NOT NULL default '0',
`Flags`
set('0x1','0x2','0x4','0x8','0x10','0x20','0x40','0x80','0x100','0x200','0x400','0x800','0x1000','0x2000','0x4000','0x8000','0x10000
','0x20000','0x40000','0x80000','0x100000','0x200000','0x400000','0x800000','0x1000000','0x2000000','0x4000000','0x8000000','0x10000
000','0x20000000','0x40000000','0x80000000') NOT NULL default '',
PRIMARY KEY (`FileID`,`ObjectID`),
KEY `ObjectID` (`ObjectID`)
) TYPE=MyISAM COMMENT='Spells'
CREATE TABLE `Classes` (
`FileID` smallint(5) unsigned NOT NULL default '0',
`ObjectID` int(10) unsigned NOT NULL default '0',
`Name` varchar(32) NOT NULL default '',
`AttrID_Pri0` tinyint(4) NOT NULL default '0',
`AttrID_Pri1` tinyint(4) NOT NULL default '0',
`SpecID` tinyint(4) NOT NULL default '0',
`SkillID_Maj0` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Maj1` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Maj2` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Maj3` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Maj4` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min0` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min1` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min2` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min3` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min4` tinyint(3) unsigned NOT NULL default '0',
`Flags`
set('Playable','0x2','0x4','0x8','0x10','0x20','0x40','0x80','0x100','0x200','0x400','0x800','0x1000','0x2000','0x4000','0x8000','0x
10000','0x20000','0x40000','0x80000','0x100000','0x200000','0x400000','0x800000','0x1000000','0x2000000','0x4000000','0x8000000','0x
10000000','0x20000000','0x40000000','0x80000000') NOT NULL default '',
`Services`
set('Weapons','Armor','Clothing','Books','Ingredients','Picks','Probes','Lights','Apparatus','Repair
Items','Miscellaneous','Spells','Magic
Items','Potions','Training','Spellmaking','Enchanting','Repair') default NULL,
`Description` text,
PRIMARY KEY (`FileID`,`ObjectID`),
KEY `ObjectID` (`ObjectID`)
) TYPE=MyISAM COMMENT='Classes'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
