Work with INDEX in MySQL

In MySQL, 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 tables which keeps primary key or index field and a pointer to each record in to 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 to in the middle of the data file outside 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 Engine to locate records very fast. VARCHAR and CHAR are considered the same if they are declared as the same size.

MySQL uses indexes follows for these operations :

• Indexes retrieve rows from other tables when joins is performing
• MySQL normally uses the index that finds the smallest number of rows
• Indexes eliminate rows from consideration
• It find out rows with matching a where clause fastly
• Mysql can use indexes on column 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 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