5

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 .

Night Walker
  • 20,638
  • 52
  • 151
  • 228
  • 1
    I think Key is an Index, Index makes query faster, if you add multiple indexes it's gonna fast up multiple columns, the downside is since now there are multiple columns more data means fewer data in memory and more on disk, so slower query. – amitnair92 Aug 28 '21 at 17:34

3 Answers3

4

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.

Community
  • 1
  • 1
Ed Guiness
  • 34,602
  • 16
  • 110
  • 145
3

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.

Neal Donnan
  • 1,733
  • 1
  • 13
  • 18
1

If you want to get data from your table, based upon the key or index fields (as said above), your query will execute faster, because the table will keep indexes about those values.

But, there is a downside to this as well. Adding unneeded indexes will have a negative effect on your performance. So to sum this all up: Indexes can speed up your database, but always check if they are really needed before adding them.

Jens
  • 3,249
  • 2
  • 25
  • 42
  • 1
    In general, is it ok to define a table with out a key column if i don't want those optimizations , and unique thing not interest me ? – Night Walker Nov 29 '10 at 09:25
  • 2
    Yeah sure. Most of the times, tables are created without indexes, which could be added later on if perfomance issues appear. – Jens Nov 29 '10 at 09:27