for example i have a users table.
user_id is my primary key and auto increment
username unique
email unique
for unique columns if i set index, is this is good or bad.
i am not asking for specific database. you can write for oracle,mssql or mysql.
thanks.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Oracle is one of these RDMS. It will have automatically created an index. This is good practice.
If you are searching the table on other columns then you should probably make an index on those columns to.
Tables can have more than one inex.
Many DBAs will create indexes on foreign keys to. Just a wee tip.
Hi phparmy,
Its bad idea to place indexes on every unique field because indexs fields also have some cost (overheads) in database operation. Its slowdown the insert, update and delete operation. But make fast
data retriever. So whenever you need to cover any field with index also look at its performance. so may be you get much performance by using only one index. ;)
please read such topic befor use it.
http://www.mysqlperformanc
Regards,
Haroon
unique indexes have very good selectivity, but depends on situation, not always putting index on this columns will have positive effect,
for mssql
If the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. Creating a unique index (preferably by creating a UNIQUE constraint) is recommended in this case.
haroon-rashid
http://www.mysqlperformanc
thanks for link. finally in this article says like below.
Finally let me mention the case when Index merge works much better than multiple column indexes. This is in case
you're using OR between the columns. In this case the combined index is useless and MySQL has an option of doing full table scan or doing the Union (instead of intersection) on values it gets from the single table.
i think i am going to use index for unique columns. because generally where i user unique column like users table where its username or email and these values not inserting or updating very often.
thanks everybody for help.
Business Accounts
Answer for Membership
by: jamesrhPosted on 2009-10-10 at 19:09:41ID: 25544644
On most RDBMs, unique columns are actually enforced via Indexes. When you mark a column as unique the system creates an index for you.