MySQL Index – Should an Ordered Integer Column Be Indexed?

database-designindexMySQL

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?

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.