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.

If my table only has two columns (username, groupname) and a million or so rows, will an index improve performance at all? Wouldn't it take just as long to search the index file as it would to search the table itself? In this case nothing is unique, a username can occur multiple times with different groupname's, and vice versa. Searches will usually just be for username.

share|improve this question
    
You don't mention if you're using InnoDB or MyISAM. I'm going to assume InnoDB. You can learn about InnoDB indexes here. What is your primary key? –  Vérace Jul 12 at 4:29
    
No key yet. That's what I'm wondering: is it any faster to search an index file with a million rows, or the table itself? –  pinhead Jul 12 at 6:46
    
For finding individual records, an index will undoubtedly be quicker. An index is pre-sorted. If I were you, I would create a PRIMARY KEY on username, groupname and then test for yourself - or is the combo username, groupname not unique? –  Vérace Jul 12 at 6:58
1  
An index is probably stored as a B-tree requiring O(log n) time to search for data vs O(n) by going through the table. See en.wikipedia.org/wiki/B-tree –  Colin 't Hart Jul 12 at 7:26
    
is username+groupname unique?, if yes then you can apply index on both the columns as composite index and change your search query to use both the columns which will be improve your query performance.if possible Read about clustered index –  Nachiket Kate Jul 12 at 8:35

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.