Hmmm... I fixed the problem but I still don't know what caused it. That same query now
produces the below EXPLAIN result and now
runs 70% faster.
I ran OPTIMIZE TABLE on Spells and Classes, that made Spl rows 1, then I forced the
Cls table to use the Object index and it worked.
Now, I can't get it to reproduce the problem. The database was just created and
populated, so it couldn't have been DB degradation.
Any ideas?
Chris
+---------+--------+------------------+----------+---------+-------------------------------------+------+---------------------------
-------------------+
| 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
| 1 |
|
| ObjSpl | eq_ref | PRIMARY | PRIMARY | 8 |
const,Spl.FileID,Spl.ObjectID | 1 | Using where; Using index
|
| Cls | ref | PRIMARY,ObjectID | ObjectID | 4 | NPCs.ObjectID_Class
| 1 | Using where
|
| ObjCls | eq_ref | PRIMARY | PRIMARY | 8 |
const,Cls.FileID,Cls.ObjectID | 1 | Using where; Using index
|
+---------+--------+------------------+----------+---------+-------------------------------------+------+---------------------------
-------------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
