Work with INDEX in MySQL

Introduction

In MySQL, an index is a data structure. It improves the functionality of the table operations. Indexes have something extra that we can enable on our mysql tables to increase performance, but they do have some outsides. Indexes are used to find rows with specific column values fastly. Indexes are also a type of table that keeps a primary key or index field and a pointer to each record in the actual table. To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key.

MySQL can instantly resolve the position to follow in the middle of the data file outside of having to look at all the data. MySQL normally uses the index that finds the smallest number of rows. They are declared as the same type and size. The users cannot see the indexes; they are just used to speed up queries and will be used by Database Search Engines to locate records very fast. VARCHAR and CHAR are considered the same if they are declared as the same size.

MySQL uses indexes follow for these operations.

• Indexes retrieve rows from other tables when joins are performing
• MySQL normally uses the index that finds the smallest number of rows
• Indexes eliminate rows from consideration
• It finds out rows with matching a where clause fastly
• Mysql can use indexes on columns more efficiently
• Strings are automatically prefix- and end-space compressed
• Indexes where, as SELECT statements become fast on those tables

The syntax for CREATE INDEX is as follows

mysql> create table headofstate(
-> id int(6) unsigned NOT NULL,
-> lastname char(20) NOT NULL,
-> firstname char(20) NOT NULL,
-> countrycode char(10) NOT NULL,
-> inauguration DATE NOT NULL
-> );
mysql> CREATE UNIQUE INDEX index_name
->        ON table_name ( column1, column2,...);

Creating a Table with an Index

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

Create index on a column

mysql> create INDEX index_1 On product (product_name);
mysql> describe product;

Displaying INDEX Information

We can use the SHOW INDEX command to list out all the indexes associated with a table. Vertical-format output (specified by \G) often is useful with this statement to avoid long line wraparound.

Example

mysql> SHOW INDEX FROM table_name\G