February 21, 2011 at 2:22 pm
I have login table.. which consists the follwing columns
user_id int autoincrement primary key
user_name varchar(30) unique key
password varchar(30)
:
:
:
while login i checks
select * from login_table where user_name=username and password=pwd and status=1
To make this query efficient. can i add non-clustered index on USER_NAME column?... should i select unique in non-clustered index?.. if select unique in non-clustered indx should i remove unique key?... any help pls?
February 21, 2011 at 2:35 pm
MonsterRocks (2/21/2011)
I have login table.. which consists the follwing columnsuser_id int autoincrement primary key
user_name varchar(30) unique key
password varchar(30)
:
while login i checks
select * from login_table where user_name=username and password=pwd and status=1
To make this query efficient. can i add non-clustered index on USER_NAME column?... should i select unique in non-clustered index?.. if select unique in non-clustered indx should i remove unique key?... any help pls?
1- Yes, you should have an index.
2- If possible, index should be on (user_name, password, status) so to have a covered index and not even access the table.
3- You should not select *, just select user_name so to allow for covered index.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 21, 2011 at 3:40 pm
You should probably consider a unique index on username (honestly, I'd make that the primary key and cluster on it).
As a side note, you might want to consider using a hash on the password. Data security and all that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply