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

 

Properties:

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:

mysql_indexes_html_m53d651ed

 

 

Leave a comment

Your email address will not be published.


*