What is indexes and how to create it in mysql?

What is indexes in mysql?


A database index is a data structure that improves the speed of operations in a table. A mechanism to locate and access data within a database.


More about index:

1) Speed up data retrieval

2) Speed of selects

3) Rapid random lookups


Expensive for:

1) Slow down writes

2) Heavy write application

3) More disk space used



1) Index can be created on One or more column

2) Index only contains key fields according to which table is arranged.

3) Index may be unique or non unique


Types of Indexes:

1) Column Index

2) Concatenated Index

3) Covering Index

4) Partial Index

5) Clustered/Non-clustered Index


1) column Index:

  • It is index on single column
  • Only those query will be optimized which satisfy your criteria

2) Concatenated Index:

  • Index on multiple columns
  • Use appropriate Index

3) Covering Index:

  • Covers all columns in query

Creating a MySQL Index – New Table

mysql_indexes_html_m53d651ed mysql_indexes_html_m650861b9


Creating a MySQL Index On Existing Table:




Leave a comment

Your email address will not be published.