Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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]

share|improve this answer
    
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? –  stanleykylee May 17 '12 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) –  Derek Downey May 17 '12 at 15:25
    
Actually I just used <>0 for demonstration purposes, it is variable inside my app. –  john.locke May 17 '12 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.

share|improve this answer
    
+dezso Do you think: KEY user_id (user_id) is redundant? –  john.locke May 17 '12 at 14:15
    
@john.locke: it seem like that. I found this reading very useful: use-the-index-luke.com/sql/where-clause/the-equals-operator/… –  dezso May 17 '12 at 14:24
    
+1 for the link, it was useful thanks. –  john.locke May 17 '12 at 14:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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