Tell me more ×
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.
up vote -2 down vote favorite

This scenario occurs so many times (for almost every website) that I thought I would ask here: What would be best indexing for a users table:

Edit: I think index on email-addresses and username may not be needed because it is set up with UNIQUE constraint and Oracle implicitly indexed unique constraint columns. I don't need to index hashed-password because as someone pointed out, that seldom occurs.

What other indexes then, if at all?

share|improve this question
@ypercube No, I did not mean to suggest there can be only one index. I only meant that these are the indexes I have and is it okay? (In oracle, primary-key in indexed by default, but I think unique columns are indexed by default too.) I'll edit my question. – user1831003 Dec 4 '12 at 8:41
3  
I think it's fine. Deleting my previous comment. People will probably ask what other columns does the table have, so it would good to add the table definition (CREATE statement) in the question. – ypercube Dec 4 '12 at 8:44
1  
Indexes are useful to speed up queries and constraints. Without knowing how you're accessing those tables, there's really no way to tell you what indexes would be potentially good. – Mat Dec 4 '12 at 8:46
1  
Adding the password hash to the user-index looks strange to me. Maybe it speeds up finding the password of the user when he wants to login but login is an action that seldom occurrs and it must not be really fast. – miracle173 Dec 4 '12 at 9:05
2  
Please, add the information requested (table definition, typical queries) Without that this is just a rhetorical question. – dezso Dec 4 '12 at 10:43
show 4 more comments

closed as not a real question by Jack Douglas♦ Dec 4 '12 at 11:00

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, see the FAQ.

1 Answer

up vote 0 down vote

Indexing that should be used on "users" should be decided according to the structure of "users", data profile of "users" and usage of "users".

share|improve this answer

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