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.

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?
–
stanleykyleeMay 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 DowneyMay 17 '12 at 15:25

Actually I just used <>0 for demonstration purposes, it is variable inside my app.
–
john.lockeMay 17 '12 at 15:32