* Colin Anderson [...] > SELECT a.name, b.value, bb.value > FROM characters a, char_data b, char_data bb, char_data_type c, > char_data_type cc > WHERE a.id = b.character_id > and a.id = bb.character_id > and (b.char_data_type_id = c.id > and b.value = '18' > and c.type = 'Strength') > and (bb.char_data_type_id = cc.id > and bb.value = '20' > and cc.type = 'Age') > > This works, but it's much too slow. This needs to be able to handle a > number of characters in the millions.
You did not mention anything about indexing...? I suppose characters.id, char_data.id and char_data_type.id are primary keys, but have you also defined an unique index on the combination character_ID and char_data_type_ID? I would drop char_data_type.id and make (character_ID, char_data_type_ID) the primary key. Then I would add an index for (char_data_type_ID, value). char_data_type.type also needs to be indexed. -- Roger query --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
