Best practices for using MySQL indexes?

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

The best practices for using MySQL indexes are.

• If a table is very large as a CSV, then using indexes will insert the records at the end.

• The indexes create a sequence of rows of a table.

• Indexes speed up certain select operations

The INSERT and UPDATE statements take more time on tables having indexes, whereas the SELECT statements become fast on those tables. The reason is that while doing insert or update, a database needs to insert or update the index values as well.

Example of MySQL indexes: If a CSV file is spread out, then indexes maintain the ordering of rows.

Let us see an example to create an index.

Create a table and indexes on a specific column.

mysql> create table IndexingDemo
   -> (
   -> Id int,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (0.86 sec)

The syntax to create an index on a table.

create index yourIndexName on yourTableName(column_name);

The following is the output.

mysql> create index indexName on IndexingDemo(Name);
Query OK, 0 rows affected (0.75 sec)
Records: 0  Duplicates: 0  Warnings: 0

Chandu yadav

Published on 20-Nov-2018 08:19:20

• Related Questions & Answers
• Best practices for Java comments.
• Best practices for writing a Dockerfile
• What are the best practices for using loops in Python?
• What are the best practices for using if statements in Python?
• Openssh security best practices
• What are the best practices for committing in Git?
• Java variable declaration best practices
• C# Exception Handling Best Practices
• Acquiring Resources – Best Practices!
• Nginx WebServer Best Security Practices
• What are the best practices for exception handling in Python?
• What are the best practices for function overloading in JavaScript?
• Integrating .NET application with SAP system and using best practices
• What are Python coding standards/best practices?
• Selenium Web Test Automation Framework Best Practices.