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