on mysql key/index

How to show a table in mysql?

In the following mysql commands, which one is correct(suppose you have already login to a database)?

  1. show tablename;
  2. describe tablename;
  3. show table tablename;
  4. describe table tablename;

The anwser is 2.

How to know if there is any index for a table?

  1. show index;
  2. describe index;
  3. show index from tablename;
  4. show key from tablename;

The answer is 3.

 

What is the difference between key and index?

There is no difference at all between key and index. They are synonyms.(http://stackoverflow.com/questions/1401572/what-are-differences-between-index-v-s-key-in-mysql)
The following two commands have the same result:
alter table tablename add key(columname);
alter table tablename add index(columname);

Must a key(index) be unqiue?

No, a key(index) can have duplicate value.

 

Can a key/index be null?

Yes.

 

What is the name of a key if I did not name it when creating it?

The name of the key is the name of the column. If you create a composite key,i.e., a key that has multiple columns, the name of the key would be the name of the first column.

How to drop a key/index?

alter table tablename drop key keyname;
If you want to remove/delete the primary key, use alter table tablename drop primary key;

What is a unique key?

A unique key is a key that won’t have duplicate value except duplicate NULL.  So the difference between a normal key and a unique key is that normal key can have duplicated value but unique key cannot have.

What is primary key and what is the difference between primary key and unique key?

A primary key is a unique key first, plus a primary key may not have null value.
But if you try to add primary key to column with a null value, the error message is confusing: ERROR 1062 (23000): Duplicate entry ” for key ‘PRIMARY’. It seems you got duplicate nulls, but actually, one null can prevent you from creating the primary key.

Is there a “create index…” command?

No.

Why do I get “specified key was too long, max key length is 767 bytes partial” error?

A primary key has a length limit for the  performance consideration. If you specify a utf-8 varchar column as the primary key, the field can have a maximum characters of 255. If the field is longer than 255 characters, you can create a partial primary key for that column as follows:
alter table tablename add primary key(columnname(255));
Or, you can just create an ordinary key for that column:
alter table tablename add key(columnname);
An ordinary key also only uses the first 255 characters for indexing.

Posted in tips of hosting