SQL/MySQL/Table Index/Index

Create index on a column

   <source lang="sql">

/* 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;

      </source>
   
  

Create table: alter table to drop index

   <source lang="sql">

/* 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;

      </source>
   
  

Create table: Make index

   <source lang="sql">

/* 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;

      </source>
   
  

Creating a Table with an Index

   <source lang="sql">
 

Drop table CD; CREATE TABLE CD (

  ID SMALLINT UNSIGNED NOT NULL,
  Name VARCHAR(40) NOT NULL,
  INDEX (Name)

); Describe CD;

      </source>