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?