What type of Index would be suitable

Question

Post reply

What type of Index would be suitable

.Netter

SSCrazy

Points: 2476
More actions
September 20, 2015 at 6:16 am

#307812

I'm on the final stretch of releasing my site, few things I'm wanting to do this week before go live.
I have gone through the database and set all the relationships between each tables i.e foreign keys.
Next step I want to add the relevant indexes to each table which will help with performance, one of these tables in particular is the profile table, when the user logs in he/she will submit their email address and password I want to create a index on these two field i.e Email Address column and Password column but i'm unsure what index to use, now I'm not an expert in SQL I'm more of a novice so any help with information around that answer would help me greatly as I have a few tables to get through. 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Assuming your query looks something like this:

SELECT UserID FROM Users where UserEmailAddress = @EmailAddress AND HashedPassword = @PasswordHash

then you probably want a nonclustered index on EmailAddress, HashedPassword.

The clustered index is probably already on whatever column is the primary key of the table, as that is the default. Whether it's the best place for it is a long discussion, but we don't have enough information to start that discussion

p.s. You are storing the password hashed, right? If not, go fix that immediately and worry about performance once the obvious security problem is fixed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

.Netter SSCrazy Points: 2476 More actions · Answer 2

Thanks for the responce, that's very similar to the query I have. I'll add the non clustered indexes to those columns.

Another question regarding indexes, I have other columns like "Age" "Gender" "seeking" etc these are foreign keys to other tables would it be worth me added additionally indexes on these columns? Or are foreign key enough? These columns in question will be used within a search so ideally I want to squeeze the most out of these select statements.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

.Netter (9/20/2015)
Thanks for the responce, that's very similar to the query I have. I'll add the non clustered indexes to those columns.

Err, no. You'll add A nonclustered index on those columns.

http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/

I have other columns like "Age" "Gender" "seeking" etc these are foreign keys to other tables would it be worth me added additionally indexes on these columns?

Maybe, maybe not. No where near enough information.

http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/

p.s. Emphasising because it's SO important. You're storing the passwords hashed, not plain text?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 4

GilaMonster (9/20/2015)
p.s. Emphasising because it's SO important. You're storing the passwords hashed, not plain text?

I find it scary on sites where I do a "lost password" request and I then get an email containing my original password 🙁

.Netter SSCrazy Points: 2476 More actions · Answer 5

All passwords are encrypted, I would never store them as plain text.

My Mistake I meant a nonclustered Index to those columns.

In regards to your other response i.e Maybe, maybe not. No where near enough information.

I have 8 columns in the database that are of type tinyint this columns are searchable from the UI so the user will come along and choose some values from the drop downs which correspond to the values in the database and in return will display all profiles that match what the user has searched on.

Now each of those 8 columns is a foreign key to its relevant parent table, now based on what you have suggested for the email and password column as I'm doing a look up then would I be right in saying I can add a nonclustered index to those 8 columns as well because realistically I'm again doing a look up?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

.Netter (9/20/2015)
All passwords are encrypted, I would never store them as plain text.

Shouldn't be. They should be hashed, not encrypted. Encryption is reversible, a password once stored should not be retrievable in its original form.

I have 8 columns in the database that are of type tinyint this columns are searchable from the UI so the user will come along and choose some values from the drop downs which correspond to the values in the database and in return will display all profiles that match what the user has searched on.
Now each of those 8 columns is a foreign key to its relevant parent table, now based on what you have suggested for the email and password column as I'm doing a look up then would I be right in saying I can add a nonclustered index to those 8 columns as well because realistically I'm again doing a look up?

Again, maybe, maybe not. See the post I linked and read through my other indexing posts.

Indexes on foreign keys (single column indexes) are useful for update/delete integrity checks, much less useful for selects unless you're filtering on that one foreign key column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101101 More actions · Answer 7

SQL provides some help here.

You can use missing index views

sys.dm_db_missing_index*

and the index stats view:

sys.dm_db_index_usage_stats

to help determine what indexes to create.

First and absolutely foremost, you need to determine and implement the best clustered index for each table.

After that, use that stats above to determine if nonclustered index(es) are also needed.

That is, on any table where you already have the best clustered index, you can use the stats to directly start creating nonclus index(es). On tables where you need to change the clustered index, I recommend removing all nonclus indexes you're not sure about and running for a while, then using new stats to re-evaluate nonclus index(es).

Unfortunately, there's no simple formula to directly determine the best index(es) from the stats, particularly for nonclus indexes. Some knowledge, experience and judgment are required. But naturally over time those will develop.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".