Hello! This is my first time on this list, so forgive me if it is not he correct place to discuss things like this. I have a question and answer tables right now that have grown large enough that my querying of them has become extremely slow and was wondering if anyone had tips on ways of optimizing for known queries.
Here is a simplified view of what I currently have: QuestionsTable id, text --------------------------- 1, What is your Gender? 2, What is your Age? 3, Rank these foods QuestionsOptionsTable id, qid, text, type --------------------------- 1, 1, Male, row 2, 1, Female, row 3, 2, 18, row 4, 2, 19, row 5, 2, 20, row 6, 2, 25, row 7, 2, 30, row 8, 3, Great, column 9, 3, Good, column 10, 3, Bad, column 11, 3, Chinese,row 12, 3, Mexican,row 13, 3, Italian,row Respondents id, name --------------------------- 1, John 2, Susie 3, Bob ResponsesTable rid, qid, oid1, oid2 --------------------------- 1, 1, 1, NULL # John, Male 1, 2, 6, NULL # John, 25 1, 3, 11, 8 # John, Chinese is Great 1, 3, 12, 9 # John, Mexican is Good 1, 3, 13, 9 # John, Italian is Good 2, 1, 2, NULL # Susie, Female 2, 2, 7, NULL # Susie, 30 2, 3, 11, 10 # Susie, Chinese is Bad 2, 3, 12, 9 # Susie, Mexican is Great 3, 2, 6, NULL # Bob, 25 3, 3, 11, 8, # Bob, Chinese is Great 3, 3, 12, 10 # Bob, Mexican is Bad The types of queries I need to execute on a regular basis are rolling up question counts and comparing them, but the actual query is ad-hoc, so I can't easily statically update counts every time a new response comes in. An example query would be: Query: "Show me a comparison of Question 3 between Male and Female between the ages of 20-30" Answer: Males, 25, Chinese, Great, 2 <-- Total count (Bob and John both answered this) Females, 30, Chinese, Bad, 1 Females, 30, Mexican, Great, 1 etc... (text instead of ids shown here to ease understanding) What I'm wondering is if there is a more denormalized view of this type of data that would make those of types of queries quicker? Thanks, John
