cant figure out some mysql commands

[ancelotp]

hi,
i'm new to sql
i'd appretiate if someone would helpme out with this doudt i have

CODE:
CREATE TABLE entry (
uno int(6) NOT NULL auto_increment,
fname varchar(30) NOT NULL,
sname varchar(30) NOT NULL,
email varchar(30) NOT NULL ,
college varchar(30) NOT NULL,
dob varchar(9) NOT NULL,
id varchar(10) NOT NULL,
mobile varchar(20) NOT NULL,
dom varchar(30) NOT NULL,
sex varchar(30) NOT NULL,
verified varchar(3) NOT NULL,
PRIMARY KEY (uno),
UNIQUE uno (uno),
KEY uno_2 (uno)
)
i totally cant understand the last two lines of code in this create
statement :
UNIQUE uno (uno),
KEY uno_2 (uno)
.... got it from an undocumented project i am working on
i need help immediately please help

[Bill Karwin]

an******@gmail.com wrote:

i totally cant understand the last two lines of code in this create
statement :
UNIQUE uno (uno),
KEY uno_2 (uno)

"KEY" is a synonym for "INDEX". It just means that the column `uno` is
indexed.

"UNIQUE" is a unique constraint; all values in the column must be
distinct. This also implies the creation of an index on the `uno` column.

It appears in this case that both of these are redundant and
unnecessary, since you already have the `uno` column declared as a
PRIMARY KEY, which implies both an index and a unique constraint.

But in some older versions of MySQL, you had to explicitly create an
index on a column before declaring a foreign key constraint on that
column. In more recent versions, declaring a FOREIGN KEY does it for you.

I don't think it was ever necessary to create an index for a PRIMARY
KEY, the declaration of that constraint automatically creates a unique
index. But the designer of the database may not have known this.

I don't think it hurts anything to have these redundant indexes, but it
uses space that you don't need to use, and may have effects on the query
optimizer. You should be able to drop the redundant indexes safely:

ALTER TABLE DROP INDEX uno, DROP INDEX uno_2;

Regards,
Bill K.

[ancelotp]

hi
thanks so much for that man
i new that code was fishy
Bill Karwin wrote:

an******@gmail.com wrote:

i totally cant understand the last two lines of code in this create
statement :
UNIQUE uno (uno),
KEY uno_2 (uno)

"KEY" is a synonym for "INDEX". It just means that the column `uno` is
indexed.

"UNIQUE" is a unique constraint; all values in the column must be
distinct. This also implies the creation of an index on the `uno` column.

It appears in this case that both of these are redundant and
unnecessary, since you already have the `uno` column declared as a
PRIMARY KEY, which implies both an index and a unique constraint.

But in some older versions of MySQL, you had to explicitly create an
index on a column before declaring a foreign key constraint on that
column. In more recent versions, declaring a FOREIGN KEY does it for you.

I don't think it was ever necessary to create an index for a PRIMARY
KEY, the declaration of that constraint automatically creates a unique
index. But the designer of the database may not have known this.

I don't think it hurts anything to have these redundant indexes, but it
uses space that you don't need to use, and may have effects on the query
optimizer. You should be able to drop the redundant indexes safely:

ALTER TABLE DROP INDEX uno, DROP INDEX uno_2;

Regards,
Bill K.

[ancelotp]

an******@gmail.com wrote:

hi
thanks so much for that man
i new that code was fishy
Bill Karwin wrote:

an******@gmail.com wrote:

i totally cant understand the last two lines of code in this create
statement :
UNIQUE uno (uno),
KEY uno_2 (uno)

"KEY" is a synonym for "INDEX". It just means that the column `uno` is
indexed.

"UNIQUE" is a unique constraint; all values in the column must be
distinct. This also implies the creation of an index on the `uno` column.

It appears in this case that both of these are redundant and
unnecessary, since you already have the `uno` column declared as a
PRIMARY KEY, which implies both an index and a unique constraint.

But in some older versions of MySQL, you had to explicitly create an
index on a column before declaring a foreign key constraint on that
column. In more recent versions, declaring a FOREIGN KEY does it for you.

I don't think it was ever necessary to create an index for a PRIMARY
KEY, the declaration of that constraint automatically creates a unique
index. But the designer of the database may not have known this.

I don't think it hurts anything to have these redundant indexes, but it
uses space that you don't need to use, and may have effects on the query
optimizer. You should be able to drop the redundant indexes safely:

ALTER TABLE DROP INDEX uno, DROP INDEX uno_2;

Regards,
Bill K.

hi
i still dont get what the uno_2 is
is it the name for the index variable as different from the variable or
is it some sql convention

[Bill Karwin]

an******@gmail.com wrote:

i still dont get what the uno_2 is
is it the name for the index variable as different from the variable or
is it some sql convention

Indexes have names, so you can specify the index when you want to drop it.

Sometimes indexes are automatically assigned a name. For instance, in
MySQL the index for a primary key is always named simply "PRIMARY".

Regards,
Bill K.

[ancelotp]

so that means i can index two columns with the same name
like
*
KEY id_2(id),
KEY id_2(email)
*????????????????????
Bill Karwin wrote:

an******@gmail.com wrote:

i still dont get what the uno_2 is
is it the name for the index variable as different from the variable or
is it some sql convention

Indexes have names, so you can specify the index when you want to drop it.

Sometimes indexes are automatically assigned a name. For instance, in
MySQL the index for a primary key is always named simply "PRIMARY".

Regards,
Bill K.

[Bill Karwin]

an******@gmail.com wrote:

so that means i can index two columns with the same name
like
*
KEY id_2(id),
KEY id_2(email)
*????????????????????

If you want a compound index comprised of two columns, the way to
declare it is:

KEY id_2 (id, email)

Note that the order of the columns in a compound key is significant. If
you were to search this table based solely on email, it would not be
able to use the index. If you search based on id, or an expression
including both id and email, it can use the index.

Regards,
Bill K.