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,
 `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

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?

2 Answers 2


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]

  • Just wanted to ask, should the WHERE clause for final_point and total_rank be any number (not <>0), an index on these will help? Commented May 17, 2012 at 15:20
  • If the WHERE were constants, it would still depend on the size of the table and what cardinality of the values in the columns. Even on a 100mil row table, I'd say an index just on user_id would suffice. You don't want too many indexes: affects write performance and, for innodb, greatly increases the storage size of the table's .ibd file (if you have the innodb_file_per_table option turned on) Commented May 17, 2012 at 15:25
  • Actually I just used <>0 for demonstration purposes, it is variable inside my app.
    – Alireza
    Commented May 17, 2012 at 15:32

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.