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
    If the primary key of the table is (user_id, question), you already have an index and don't need another. – ypercubeᵀᴹ 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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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