Back to Top

How to use Index in MySQL

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.

Earlier I have explained about MySQL User Defined Functions and Table Optimization in MySQL.Now it is time to explain about INDEX in MySQL

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.

Also Read:MySQL FLUSH Commands

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:

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:

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:

3.SPATIAL

SPATIAL index supports spatial column and is available only on MyISAM storage engine. Also, the column 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:

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:

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.

Read about: MySQL coalesce() function

Show INDEX:

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

SYNTAX:

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:

index type in 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.

If this post helped you, you should consider subscribing via feeds or e-mail, because it’s the best way to get more information and feature articles.

Share:
Share on Facebook3

Share on LinkedIn0Tweet about this on TwitterShare on Google+0Buffer this pageDigg thisShare on Reddit0

Leave a Reply

Your email address will not be published. Required fields are marked *

Most Popular Posts

Static Methods and Properties in PHP

Posted on 5 years ago

Bhumi

How to clean HTML content in PHP?

Posted on 3 years ago

Bhumi

Warning: Ignoring WordPress Can Be a Blunder

Posted on 4 years ago

Nick Carter

MySQL coalesce() function

Updated 17 years ago

Bhumi

How to use JavaScript Promise API?

Updated 17 years ago

Bhumi

What are Lambdas and Closures in PHP?

Updated 17 years ago

Bhumi

An Introduction to Gulp JS

Updated 2 months ago

Bhumi

The Reader’s Poll – June 2015

Posted on 2 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 3 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 4 years ago

Bhumi