difference between key column and non key one

Question

Sorry for the newbie question. I have define a table in a following way

CREATE TABLE `class1` (
`name` varchar(20) NOT NULL,
`familyname` varchar(20) NOT NULL,
`id` int(11) DEFAULT NULL,
KEY `class1` (`name`,`familyname`));

Can you explain me what is the difference here that i have here a name and family name as key.

I have to enter values to the table and run queries but i can't understand what it gives me ? If i not define name and family name as keys i get the same results .

Answer 1

A key, otherwise known as an index, will not change the results of a query, but sometimes it can make the query faster.

If you're interested, you can read about different kinds of keys here.

Answer 2

The KEY keyword in the create table syntax in mysql is a synonym for INDEX and will create an index on the 'name', 'familyname' columns.

This will not effect any constrains on the table but will make a query on the table faster when using the 'name' and 'familyname' columns in the where clause of a select statement.

If you wanted to create a primary key on those 2 columns you should use:

CREATE TABLE `class1` (
`name` varchar(20) NOT NULL,
`familyname` varchar(20) NOT NULL,
`id` int(11) DEFAULT NULL,
PRIMARY KEY (`name`,`familyname`));

This will stop you being able to insert multiple rows with the same 'name' and 'familyname' combination.