HOW TO USE INDEX IN MYSQL

MySQL is a pretty fast database but sometimes you have larger data so to search out anything from those data is take a time to get the result.

Introduction about Index

The index is the way to speed up searches of large query sets in MySQL.Indexes are mainly used to find rows of a table with a specific value for a column quickly.The index stores the mapping between the value of the column and the physical location of the row in MySQL.

The database examines a complete scan of the table without indexes and it a large dataset, a query takes much more time to find specific value from the table which leads to lower performance.Indexes are useful to search rows with a specific value for a column and if the table is too large.

MySQL can jump directly to the approximate location of your data as much more quickly with the use of Index.MySQL requires at least one index on every table to optimize the query.

SYNTAX:

1 2 3

CREATE [UNIQUE|FULLTEXT|SPATIAL]INDEX index_name ON table_name ( column1, column2,...);

A field provides a unique index is a way to guarantee that the integrity of the data has been maintained. Whenever a SELECT statement has a WHERE condition that filter on unindexed fields may require full table scans.

PHPMyAdmin provides a two ways to create an index in MySQL. First, if the index applies to a single column after you go to the structure page for a table and click the index icon on the same line of the column name. and the second option is you can use the index management page.In that, you can select which columns will be used as the index and create it.

INDEX Types

There are a 3 types of indexes that specify how the indexes work.Let’s understand the effect of making an index work better or worse.

1. UNIQUE
2. FULLTEXT
3. SPATIAL

1. UNIQUE:

UNIQUE Index is used to create a constraint that all values in the index must be unique in MySQL.You can use UNIQUE without having it as a primary key.A UNIQUE attribute field can serve as a data integrity check and relieve you to performing data check programmatically.

A unique index can be specified in MySQL like this:

1 2 3

ALTER TABLE table_name ADD UNIQUE my_index ('column_name')

NOTE: Duplicate NULL value is allowed in all storage engine except BDB.

2. FULLTEXT

The FULLTEXT index was available in MySQL 3.23.23 and operates only on the default MySQL table type of MyISAM. MySQL 4.0+ versions support some additionally configured parameters.

FULLTEXT index is supported only on MyISAM Storage Engine and only accepted on the column that has data type is VARCHAR, CHAR, and TEXT. FULLTEXT index will not operate on binary fields of any type. It will ignore some words such as a, an, and moreover any words that appear in greater than 50 percent of the existing records.

INSERT into a table indexed with FULLTEXT index can be significantly slow.You can use FULLTEXT on any size field. FULLTEXT index is greatly simplified and speeds up searching against large data of text.

You need to use a special sort of SELECT statement called a MATCH to take advantage of the FULLTEXT index.

Let me explain it with example:

1 2 3

SELECT * from table_name WHERE MATCH(column_name) AGAINST('word')

3.SPATIAL

SPATIAL index supports spatial column and is available only on MyISAM storage engine. Also, thecolumn value must not be NULL.

Create index in MySQL

In your database, create one column in the users table to the index by using the CREATE INDEX statement as follows:

1 2 3

CREATE INDEX name ON users(name)

Note: You can define indexes on more than one column on a table.

Drop INDEX:

After creating an index in MySQL, you can also drop/delete index by using DROP INDEX statement.

SYNTAX:

1 2 3

DROP INDEX index_name ON table_name

NOTE: Up to 32 indexes per table permitted.

If you want to remove the indexes from the table to improve the UPDATE and INSERT queries performance, you can use above syntax.

Show INDEX:

The SHOW INDEX command is used to displays information about the indexes exists in a particular table.

SYNTAX:

1 2 3

SHOW INDEX FROM table_name [FROM database_name]

MySQL support different types of storage engines and we mostly use MyISAM and InnoDB storage engined but here I am going to list out all the storage engines allowed index types supported by MySQL:

Try an index on anything you think might be useful and measure the performance improvement :).The more you know about indexes, the more you can optimize your databases so take advantage of the index in MySQL and avoid the drawbacks if possible.