Solved

Mysql Indexes and Primary vs Unique

Posted on 2008-11-02
4
911 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Date 6 41
Windows 10, 64 bit WAMP - won't start 9 28
Ajax success not firing alert 6 41
SQL join ...want to return one row 4 8
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

734 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