Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a need for processing some data in order to come up with some statistic. This is the scenario:

Each user can answer several questions from the question list. They pick their answers from a drop-down list, therefore the answer is predictable in terms of format.

Some users answer more or less than others

I would like to choose a specific user, and run a query on his answers. For example: I want to know how many other users answered the same questions as my user and their answers where the same. If my user answered "his name is 25", "his gender is male", and "his height is 6" then how many users are in the database that have their age as 25, their gender male, and their height as 6 ?

Maybe user x answered 5 questions, but as long as his 3 questions and answers match with my user's 3 questions and answers it's a hit. I want to discards users who answered anything less than what my user answered which means user who answered 2 questions are discarded.

Can MySQL achieve such query without cause heavy performance hits ? And if yes how can I construct such query ? Here is some basic tables I created to communicate the concept better.

CREATE TABLE Question (
  QID INT,
  QTxt VARCHAR(150),
  QOrder INT);

CREATE TABLE User (
  UID INT,
  UserName VARCHAR(150));

CREATE TABLE ANSWER (
  AID INT,
  ATxt VARCHAR(150),  
  QID INT,
  USERID INT);

I'm using this MySQL query, it works close to what I want but the "IN" doesn't completely match the exact result I'm interested in.

SELECT * FROM Answer as Ans WHERE
         Ans.QID IN (SELECT QID FROM Answer where USERID=1)
GROUP BY USERID
share|improve this question
    
How big do you expect this project will be? With the info provided, it seems MySQL will be able to take this easily, and much (much) more. Are there any particular reasons why you are considering MongoDB? –  KookieMonster Nov 21 '13 at 19:33
    
I thought maybe MongoDB is more suitable for what I want to do, but again I'm not sure. My data size is medium but will be large since the more questions users answer, the more matching needs to be done. Roughly saying, about 2 to 4 million records top, all answers. Any suggestion how to construct a query to satisfy this ? I have no idea if a join would cut it! –  AlexCon Nov 21 '13 at 19:39
    
2 to 4 million records would typically be categorized as small. Certainly not large. MySQL should be able to handle this without issue. And if not, SQL Server Express (which is free) certainly could handle it without problem. –  Max Vernon Nov 21 '13 at 20:32
    
Anyone can also offer a query solution to this ? I'm trying different queries but non of them return exact match for the questions and answers a specific user posted. –  AlexCon Nov 21 '13 at 21:23
    
You might interested in this and this –  a_horse_with_no_name Oct 30 at 18:23

1 Answer 1

This is probably going to be simpler in MongoDB because you can generalize the answers into an array and still be able to index it. Here's how you can structure it:

{ "username": "xxx", "answers" : [ { "q":"age", "a":25 }, { "q":"sex", "a":"Male" }, { "q":"height","a":6 } ] }

Now to match all users who have the same three answers (regardless of other answers) you can do in a single query:

db.users.find({"answers":{"$all":[
                          {"$elemMatch":{"q":"age","a":25 }},
                          {"$elemMatch":{"q":"sex","a":"Male" }},
                          {"$elemMatch":{"q":"height","a":6 }}
              ] } } );
share|improve this answer
    
Asya, I like this approach, Is this how I can do it in Grails ? –  AlexCon Nov 25 '13 at 15:39
    
All of MongoDB drivers have the aggregate method - grails uses Java driver under the hood and it supports "aggregate" helper. –  Asya Kamsky Nov 25 '13 at 23:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.