using index key the right way - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

using index key the right way

Author

Topic

romeo40777
Starting Member

3 Posts

Posted - 2012-05-15 : 03:20:26

I am creating a website which will have a login form with 2 fields a email and a password field would indexing the email and password field be considered good practice since my sql statement will have a "WHERE" clause pointing to those? as I have read that the email takes up too much disk space for index but that it increases performance. My other question is that I will have another table were users can post multiple times and it will also have an email and password field with an index and a primary key auto increment number will having the same email multiple times in the same table create problems ?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-05-15 : 08:51:52

hi.

these are 2 different questions:

1) Login form with email address and password:

you will definitely want to look up the user from the email address as this is the only thing you have to go on. Therefore an index on the email address field of the table will be very useful and probably very necessary.

The email address will have a high degree of cardinality (it's nice and specific) so you will get good index use out of it.

This is assuming you have a User's / Logins table that looks something like


[UserID] INT PRIMARY KEY (autonumber / identity probably)
, [EmailAddress] VARCHAR(255) NOT NULL (you should have a UNIQUE constraint on here)
, [PasswordHash] VARBINARY(...) NOT NULL (only store a hash of the password, don't store the real password)
...
...
...

to check that the user could log in then the only index you would need would be:


CREATE UNIQUE NOCLUSTERED INDEX IX_Email_PasswordHash ON <TheTable> ([EmailAddress], [PasswordHash])

Which would give you a good index seek to check someone's credentials (hash the password first in your algorythm of choice)

Your 2nd question:

quote:

My other question is that I will have another table were users can post multiple times and it will also have an email and password field with an index and a primary key auto increment number will having the same email multiple times in the same table create problems ?

Use normalization.

http://en.wikipedia.org/wiki/Database_normalization

Have a foreign key to the user table (or user_details table or whatever) instead of storing any repeating information. It's a relational database.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

gtabetnj
Starting Member

8 Posts

Posted - 2012-05-15 : 22:06:04

If its as simple as you stated, and Email address is unique, I would do an index JUST on the email address (not a clustered index, just a regular unique index) and then carry the password as a 'additional field' If you have SQL 2008 or later you can do this. Depending on the interface you are using to define your indexes, the actual title for this 'additional field' will change, but basically it is a supplementary field that does not get included in the index sorting or hashing, but is carried along with the index so you do not have to retrieve the actual row of data when you use the index to find the email it will be there already.

thanks

gej

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources