I'm starting to get pretty comfortable with mysql, but I'm still a beginner, and I've run into a problem I can't seem to get around.
I'm having trouble getting a particular query to run within anything approaching a reasonable amount of time -- I'm not sure whether it's a problem with my query structure, a problem with my schema, or an intrinsic limitation in what I'm trying to do. Here's a simplified example of my schema (theme of the data changed so as to be more intuitively understandable, hopefully) TABLE characters ID Name TABLE char_data_type ID Type TABLE char_data ID character_ID char_data_type_ID Value So then I'd have a number of characters: "Bob", "Tierney", "Ogrek the Beheader" a number of char_data_types: "Strength", "Intelligence", "Age", "Class" and a number of char_data values: "18", "10", "Rogue", etc. You can pretty easily return a list of all characters with a Strength of 18: SELECT characters.name FROM characters, char_data_type, char_data WHERE char_data.value = "18" AND char_data.char_data_type_ID = char_data_type.ID AND char_data.character_ID = characters.ID; The tricky part is returning characters restricted to certain values for two or more characteristics, i.e. a Strength of 18 _and_ an Age of 20. After some time (I hadn't heard of self joins), I came up with this: (I also want to return a list of matched values, pointless as that seems in the simplified example) 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. Selecting INTO OUTFILE 'foo' cuts the time taken by 5-10%, but that's not really sufficient. Because I need to support a large amount of data, and disk space is a priority second only to speed, I'm hesitant to switch to a single characteristics table, combining char_data and char_data.type -- I don't know how much that would help, anyway. And because the characteristic types need to be dynamically addable and removable, I can't just make extra columns in the characters table. Any suggestions? - Colin --------------------------------------------------------------------- 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
