Posted by Rik on 07/05/06 10:20

-Lost wrote:
> Hrmm... my current installation of MySQL tells me:
>
> PRIMARY and INDEX keys should not both be set for column `field`
>
> ...and actually, all I did was add the field as a UNIQUE (it was
> already PRIMARY). However, it tells me this too if I try and add it
> as INDEX.
>
> Is that the desired behavior?

Yep, UNIQUE on username, PRIMARY on userid.... 2 _different_ fields.
Creating more indexes on one field is useless. A primary index is already
unique, and an index that should be unique should be set to unique, to index
AND unique.

> In one aspect, I wish for all articles (for example) to have a simple
> numeric ID. Therefore, I use it as a PRIMARY for incrementing.
> However, if ever I clean the table or mess up with code, I do not
> want to give it the ability to write an ID that already exists
> (right, always check with the code beforehand, but in case I did
> not). Which is what I thought making it UNIQUE gave me easier
> control of.
>
> I see more and more that writing small to medium intranet
> applications has made me increasingly numb to the effects of database
> effeciency. Quite frankly, I never played with UNIQUE or INDEX until
> I read this thread. I always used PRIMARY, and did the proper
> checking of unique et cetera, with code.
>
> As a last aside, what would you give as an example for all three?
> INDEX, PRIMARY, and UNIQUE? (Example/sample table structure.)
> Thanks!


The reason for creating an INDEX is for faster searching, normally when
combining tables. The MySQL manual sais:
"Indexes are used to find rows with specific column values quickly. Without
an index, MySQL must begin with the first row and then read through the
entire table to find the relevant rows. The larger the table, the more this
costs. If the table has an index for the columns in question, MySQL can
quickly determine the position to seek to in the middle of the data file
without having to look at all the data. If a table has 1,000 rows, this is
at least 100 times faster than reading sequentially. If you need to access
most of the rows, it is faster to read sequentially, because this minimizes
disk seeks. "

TABLE users
id int(8) PRIMARY AUTOINCREMENT
name varchar(40) UNIQUE
some_other_fields

TABLE user_files
id int(8) PRIMARY AUTOINCREMENT
user int(8) INDEX
location varchar(50)
some_other_fields

where user_files.user == users.id

So the query:
"SELECT y.location FROM users x JOIN user_files y ON x.id = y.user WHERE
x.name = 'John'"
will find all files belonging to the user named John much quicker thanks to
the index on the user-field in user_files.

Grtz,
--
Rik Wasmus

[Back to original message]