2

I have a table like the picture below, I want to index two columns of this table (user_id, question). The type of all columns is int. The user_id and the question_id columns are foreign keys.

CREATE TABLE user (
    user_id int(10) NOT NULL AUTO_INCREMENT,
    username varchar(50) DEFAULT NULL,
    password varchar(60) DEFAULT NULL,
    PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE question (
    question_id int(10) NOT NULL AUTO_INCREMENT,
    question varchar(60) NOT NULL,
    answer varchar(60) NOT NULL,
    PRIMARY KEY (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

CREATE TABLE answer (
    user_id int(10) NOT NULL,
    question_id int(10) NOT NULL,
    score int(10) NOT NULL DEFAULT 0,
    timer int(10) NOT NULL DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES user(user_id),
    FOREIGN KEY (question_id) REFERENCES question(question_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

enter image description here

A lot of "updating" and "reading" query goes to this table. As you can see these two columns are already ordered because every time a user signup I will add the user's id in the user_id column and the numbers from 1 to 300 to the question column. the query is something like this:

UPDATE score SET user_score_for_question = 20 WHERE user_id=400 AND  question=100

Question1: since "user_id" and "question" columns are already ordered, Is it necessary to index them or not?

Question2: If I should index those columns, since I always use "user_id" and "question" together, how is the correct way to index these columns? I not familiar with "hash index" and "unique index" which one should I use?

2
  • 2
    If the primary key of the table is (user_id, question), you already have an index and don't need another. Aug 30 '16 at 15:24
  • They are not primary key. They are foreign keys. okay I will add that soon.
    – cool
    Aug 30 '16 at 16:26
1

Inserting in an order does not mean that is a natural order in the table.

Put a composite primary key on:

user_id, question_id

A second index on question_id is optional.
It would only be used if you are searching on question_id only.

2
  • Does anything wrong with this: PRIMARY KEY (user_id, question_id), FOREIGN KEY (user_id) REFERENCES user(user_id), FOREIGN KEY (question_id) REFERENCES question(question_id)
    – cool
    Sep 1 '16 at 3:25
  • Seems good to me.
    – paparazzo
    Sep 1 '16 at 3:38

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .