Should I use "unique", "index"?

[szym9341]

I'm not sure what to do... please help.

I have three tables (tableA, tableB, tableC)

tableA has a primary key, auto-increment
tableA has a column that stores tableB's primary key
tableB has a column that stores tableC's primary key

My query selects * from tableA where a field in tableC = "brown"

Should I set each linking (foreign key) column in MYSQL to index?

thanks for any help regarding proper use of index and unique

[AstroTeg]

If the IDs are set as a primary key, I believe (although I could be wrong) they will automatically be indexed.

Unique is just a constraint that says if you try to add the same chunk of data to the table (be it in the same column), the constraint will toss an error at you saying you already have that data in the table. Its a good way to enforce uniqueness. Although I don't believe it does anything for query performance like what an index would do.

An index will actually tell the database to keep a seperate "list" of your data you said to have it index. This is list organized and in order. The database will consult this list first if it has to do any queries or lookups because it can hit this list very very quickly. The disadvantage is it takes a little longer to do inserts and deletes with an index, but the time usually isn't that much unless you have some super huge tables.

[szym9341]

Thank you, thank you...

I have been reading from an article on phpbuilder for the past two hours regarding this topic... whew...

I am under the understanding that "unique" is a type of index (and when you assign a primary key, it is actually a unique index)

I still don't know when to use "unique" over regular ole' "index".