index in table

Index

Index is used to increase the performance of the query.

When creating a table definition that includes indexes, you should place the primary key columns first,followed by the unique index columns, and then followed by any non unique index columns. This process helps to optimize index performance

1.  primary key
2.  Unique key
3.  non unique key

CREATE TABLE statement defines a unique index on the OrderID and ModelID columns:

CREATE TABLE Orders
(
OrderID SMALLINT UNSIGNED NOT NULL,
ModelID SMALLINT UNSIGNED NOT NULL,
ModelDescrip VARCHAR(40),
PRIMARY KEY (OrderID),
UNIQUE (OrderID, ModelID)
);

Add a full-text index to a MyISAM table. As you recall, you can add this type of index only to the CHAR, VARCHAR, or TEXT columns.

CREATE TABLE Orders
(
OrderID SMALLINT UNSIGNED NOT NULL,
ModelID SMALLINT UNSIGNED NOT NULL,
ModelName VARCHAR(40),
PRIMARY KEY (OrderID),
FULLTEXT (ModelName)
);

Query to delete index

ALTER TABLE Orders
DROP INDEX unique_1;