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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 '14 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 '14 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 '14 at 6:58
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 – Colin 't Hart Jul 12 '14 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 '14 at 8:35

Your Answer


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

Browse other questions tagged or ask your own question.