Table Indexing

Anon

How does indexing work? I am trying to understand how I can use indexing to remove redundancies from my database, but I cannot quite get the gist of indexing. Can anyone help me with this.

[deleted]

Indexing doesn't really help against redundancy, but contraints do. MySQL speaks of 'unique' indexes where it really means 'unique constraints'.

Are you trying to avoid getting duplicate data (like two users name carl), or are you really trying to reduce redundancy by splitting data into several tables?

Anon

Hi Vincent,

Yes I am trying to avoid gettin duplicate data.

[deleted]

In that case you can createa 'unique index' on the column that you want to make unique.

Once you created that index, MySQL will nolonger let you insert duplicate data for that column. If you try to insert a duplicate anyway, you will get an error returned. This means that you MUST do error checking to see if the record you tried to insert really was inserted.

http://www.mysql.com/doc/A/L/ALTER_TABLE.html

look for the part about 'add unique'

Anon

a great article here on this site:

http://www.phpbuilder.com/columns/barry20000731.php3

helps explain database normalization which is where you're headed with indexing. It does MUCH farther than you are asking about but it might answer a few questions for ya.

:-)

[deleted]

"helps explain database normalization which is where you're headed with indexing"

how are indexing and normalization related?

Anon

This is a quote from the article:

First Normal Form
1. Eliminate repeating groups in individual tables.
2. Create a separate table for each set of related data.
3. Identify each set of related data with a primary key.

wouldn't you agree that indexing is part of #3?

anyway - just because I don't know what I'm talking about doesn't mean that the article isn't worth reading...

;-)

[deleted]

"3. Identify each set of related data with a primary key.

wouldn't you agree that indexing is part of #3?"

Not really no.

A primary key is just one type of index, there are many more.

PS:
Yes the article is definately worth reading!