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
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?
Best Answer
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.