Solved

Mysql Indexes and Primary vs Unique

Posted on 2008-11-02
4
907 Views
Last Modified: 2013-12-13
I have been working on simple database stuff.

Took the suggestions from here...

But wondering a little bit more about why.

*Indexes and Primary vs Unique*

From my reading, I have set up my table to have "userName" be Unique...

Because I don't want two values to be the same.

Not really sure why I have my "memberID" set up for Primary...and not Unique...

Now, both are indexs.

Why would I want that? Or is that just a default of what happens when I make something a Primary / Unique.

I am just guessing that it makes the data look different than the other columns when doing querys.

Thanks for the help
0
Comment
Question by:cntmedia
4 Comments
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 22862398
memberID is an integer column, uniquely identifies record and can be referenced by a foreign key. Hence, it is declared as PK & an index is created in the background. By the way foreign keys are allowed in non-default innoDB tables. Normally ID/CODE columns are made as entity-relationship keys.
userName has to be just unique but maybe even null, hence unique constraint &/ unique index is created on that.
It depends whether default is clustering index or not for a PK in the type of your table. If it is, then that will be  the only one allowed for that table. Good also, since it is PK which has got high cardinality i.e. set of unique values and does not get normally updated & nowadays people resort to soft deletion to preserve all data in table itself.
Clustered index is physically sorted on disk, hence tends to get fragmented requiring defragmentation via reorg based on how much activity occured against this index. Non-clustered indexes which are allowed more than one on a table contain data as in table column with pointers retrieving the sorted order.
You are correct, data will be different, one will be ID's i.e. integer and the other Names i.e. characters.
0
 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 125 total points
ID: 22862411
It sounds like you're asking two different questions.

First, why would a PRIMARY KEY be listed as an INDEX?  Well, that's exactly what it is!  A table's primary key is its most important index.

Every table must have exactly one primary key (i.e., it has to have one specified, and it can't have two).  Note that you can have several different FIELDS in your primary key, but there's only ever one primary key.  This key must uniquely identify every row of the table, and it shouldn't ever change (e.g., once you've assigned someone memberID #192, they should stay member #192 forever).

Generally, the most efficient query you can run over a table's data is to select a single row using its primary key.  What's the record for member #192 look like?  Easily found!

Second, what's the difference between a PRIMARY KEY and a UNIQUE index?  Essentially, you've already picked a primary key (memberID), but you've realized you also have another field in your table that you want to use to uniquely identify rows.  Since you've already named memberID as your primary key, you can designate username as an ordinary UNIQUE field.

Both should indeed get indexed, since you'd like queries for both a memberID or for a username to be particularly efficient.

Does that answer your question?
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 22865427
Primary = Primary key index
Unique = No duplicates allowed
0
 

Author Closing Comment

by:cntmedia
ID: 31512512
Thanks for the answer
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
resizeing PHP image 2 24
SQL query 4 31
encyps queries mssql 15 27
SQL Stored Procedure insert running but not inserting record 40 38
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now