Indexes

Used to increase the speed of queries

 

Type of table that stores the primary key (index field) and a pointer to each record in the target table.

 

INSERT and UPDATE commands take longer to execute, since the database also inserts or updates the index values.

 

It is recommended that indexes are only created on tables/fields that are frequently queried.

 

 

Creating an Index

 

CREATE INDEX index_label ON table_name(filed_name) ;

 

To create an index called myIndex on the customers table, allowing duplicates:

mysql> CREATE INDEX myIndex ON customers (CustID);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

 

To create a unique index called myIndex on the orders table, not allowing duplicates:

mysql> CREATE UNIQUE INDEX yourIndex ON orders (OrderID);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

 

Viewing an Index

 

SHOW INDEX FROM database.table ;

 

To view an index:

 

 

Removing an Index

 

Use DROP INDEX to remove an index:

mysql> drop index myIndex on customers;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0

Leave a Reply