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
-
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!
-
Do I have to index
final_point
andtotal_rank
to get faster select results just because they are in the where clause?
Best Answer
First, your index on
KEY user_id (user_id)
is redundant, since you have the index onUNIQUE KEY user_id_2 (user_id,iq_id)
anduser_id
is the left-most prefix.Second, you won't need to index
final_point
andtotal_rank
because the mysql optimizer would ignore the<>0
portions, and scan the entire index based onuser_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.