SQL/MySQL/Table Index/Index

Create index on a column

/*
mysql> Drop table Product;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Product
    -> (
    ->    ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
    ->    ModelID SMALLINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE INDEX index_1 ON Product (ModelID);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field   | Type                 | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID      | smallint(5) unsigned |      | PRI | 0       |       |
| ModelID | smallint(5) unsigned |      | MUL | 0       |       |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

*/
Drop table Product;
CREATE TABLE Product
(
   ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
   ModelID SMALLINT UNSIGNED NOT NULL
);
CREATE INDEX index_1 ON Product (ModelID);
Describe Product;

Create table: alter table to drop index

/*
mysql> Drop table Product;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE Product
    -> (
    ->    ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
    ->    ModelID SMALLINT UNSIGNED NOT NULL,
    ->    UNIQUE unique_1 (ID, ModelID)
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field   | Type                 | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID      | smallint(5) unsigned |      | PRI | 0       |       |
| ModelID | smallint(5) unsigned |      |     | 0       |       |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE Orders
    -> DROP INDEX unique_1;
ERROR 1091 (42000): Can"t DROP "unique_1"; check that column/key exists
mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field   | Type                 | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID      | smallint(5) unsigned |      | PRI | 0       |       |
| ModelID | smallint(5) unsigned |      |     | 0       |       |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

*/
Drop table Product;
CREATE TABLE Product
(
   ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
   ModelID SMALLINT UNSIGNED NOT NULL,
   UNIQUE unique_1 (ID, ModelID)
);
Describe Product;
ALTER TABLE Orders
DROP INDEX unique_1;
Describe Product;

Create table: Make index

/*
mysql> Drop table Product;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Product
    -> (
    ->    ID SMALLINT UNSIGNED NOT NULL,
    ->    ModelID SMALLINT UNSIGNED NOT NULL,
    ->    PRIMARY KEY (ID),
    ->    INDEX (ModelID)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field   | Type                 | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID      | smallint(5) unsigned |      | PRI | 0       |       |
| ModelID | smallint(5) unsigned |      | MUL | 0       |       |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

*/
Drop table Product;
CREATE TABLE Product
(
   ID SMALLINT UNSIGNED NOT NULL,
   ModelID SMALLINT UNSIGNED NOT NULL,
   PRIMARY KEY (ID),
   INDEX (ModelID)
);
Describe Product;

Creating a Table with an Index

  
Drop table CD;
CREATE TABLE CD
(
   ID SMALLINT UNSIGNED NOT NULL,
   Name VARCHAR(40) NOT NULL,
   INDEX (Name)
);
Describe CD;