Are these extra Index fields needed?

Question

I've designed a table as below for users who register a quiz:

CREATE TABLE `registered_quiz` (
 `rq_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` smallint(5) unsigned NOT NULL,
 `iq_id` smallint(5) unsigned NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `is_registered` char(0) DEFAULT NULL,
 `delivered` char(0) DEFAULT NULL,
 `final_point` float NOT NULL DEFAULT '0',
 `total_rank` smallint(5) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`rq_id`),
 UNIQUE KEY `user_id_2` (`user_id`,`iq_id`),
 KEY `user_id` (`user_id`),
 KEY `iq_id` (`iq_id`),
 KEY `date` (`date`),
 CONSTRAINT `registered_quiz_ibfk_1` FOREIGN KEY (`iq_id`) REFERENCES `inja_quiz` (`iq_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `registered_quiz_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `karbar` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

And I have a query as below:

SELECT final_point,total_rank,rq_id FROM registered_quiz WHERE user_id=1111 AND final_point<>0 AND total_rank<>0 

1. Do I need the part KEY user_id (user_id) ?

   I was wondering if UNIQUE KEY `user_id_2` (`user_id`,`iq_id`) would be enough!

2. Do I have to index final_point and total_rank to get faster select results just because they are in the where clause?

Answer 1

First, your index on KEY user_id (user_id) is redundant, since you have the index on UNIQUE KEY user_id_2 (user_id,iq_id) and user_id is the left-most prefix.

Second, you won't need to index final_point and total_rank because the mysql optimizer would ignore the <>0 portions, and scan the entire index based on user_id.

It is worth pointing out (even if it wasn't part of the question), that you must retain the index KEY iq_id (iq_id) because of the foreign key. If you don't have that index, the foreign key would create one exactly the same.

in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. [src]

Answer 2

Provided you have an index on user_id (or a compound index with user_id as the first column) and a user does not have to many rows, you don't have to index final_point and total_rank.