Avoiding using the same subquery multiple times in a query

Question

In an MMORPG server I am refactoring, I have two tables. One for items, and one for spells. Each item has up to 5 spells, so I went with a sparse-matrix format, having 5 columns for spell IDs.

The original devisers of this structure chose to use MyISAM which does not support referencing, causing the items table to contain items with non-existant spell IDs. I wish to find out which items have incorrect spell IDs in order to fix them and perhaps in the long run convert to InnoDB.

So far I've been able to come up with only this:

SELECT COUNT(*)
  FROM items
 WHERE spellid_1 NOT IN (SELECT entry FROM research.spell)
    OR spellid_2 NOT IN (SELECT entry FROM research.spell)
    OR spellid_3 NOT IN (SELECT entry FROM research.spell)
    OR spellid_4 NOT IN (SELECT entry FROM research.spell)
    OR spellid_5 NOT IN (SELECT entry FROM research.spell);

Is there a more elegant way to do so?

EDIT: NULL spellid_n counts as valid since it just means the item doesn't have a spell in that slot.

Answer 1

It would have been more elegant to design the tables so that you didn't have 5 spellid columns in the same table - i.e by having an item_spell table that would allow any number of spells per item. Apart from being more future-proof (when you find you now need 6 spells), your query would become:

SELECT COUNT(DISTINCT item_id)
  FROM item_spells
 WHERE spell_id NOT IN (SELECT entry FROM research.spell);

As it is, you are forced to perform the check 5 times.