Best practices for using MySQL indexes?

A database index is a data structure that can improve the speed of operations in a table. An index can be created using one or more columns, which provides a foundation for fast random lookups and effective sorting of records.

The best practices for using MySQL index are.

• If the table is very large (such as CSV), using an index will insert records at the end.

• Index creates a series of rows in a table.

• Indexes can speed up certain selection operations

INSERT and UPDATE statements take more time on indexed tables, while SELECT statements become very fast on those tables. The reason is that the database also needs to insert or update index values when performing insert or update operations.

Example of MySQL index: If the CSV file is scattered, the index will maintain the order of the rows.

Let's look at an example of creating an index.

Create a table and establish an index on a specific column.

mysql> create table IndexingDemo
　　　-> (
　　　-> Id int,
　　　-> Name varchar(100)
　　　-> );

Syntax for creating an index on a table.

create index yourIndexName on yourTableName(column_name);

The following is the output.

mysql> create index indexName on IndexingDemo(Name);
Records: 0  Duplicates: 0  Warnings: 0