Meritshot Tutorials

  1. Home
  2. »
  3. SQL Indexes
SQL

SQL Tutorial

SQL Indexes

An INDEX in SQL is a database object that improves the speed of data retrieval operations on a table. Indexes work by creating a data structure that allows the database to find rows more quickly than scanning the entire table. They can be created on one or more columns and are especially useful for columns frequently used in WHERE clauses, JOIN conditions, or sorting operations.

Syntax

— Creating an index

CREATE INDEX index_name

ON table_name (column_name);

— Creating a composite index

CREATE INDEX index_name

ON table_name (column1, column2);

— Dropping an index

DROP INDEX index_name;

  • index_name: The name of the index to be created or dropped.
  • table_name: The name of the table on which the index is created.
  • column_name, column1, column2: The columns on which the index is created.

Example:

1 Creating a Simple Index

Suppose you have a table Students and you want to create an index on the LastName column to speed up searches by last name:

CREATE INDEX idx_lastname

ON Students (LastName);

This index will improve query performance when searching or sorting by LastName.

2 Creating a Composite Index

If you often query the Students table by both LastName and FirstName, you can create a composite index:

CREATE INDEX idx_name

ON Students (LastName, FirstName);

This index helps optimize queries that use both columns in the WHERE clause or sorting operations.

3 Creating a Composite Index

If you often query the Students table by both LastName and FirstName, you can create a composite index:

CREATE INDEX idx_name

ON Students (LastName, FirstName);

This index helps optimize queries that use both columns in the WHERE clause or sorting operations.

4 Index on a Primary Key

A primary key automatically creates a unique index to enforce uniqueness:

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    FullName VARCHAR(100)

);

Here, an index is automatically created on EmployeeID to ensure all values are unique and to speed up lookups.

Tips to Remember

  1. Balance Indexes: While indexes speed up data retrieval, they can slow down INSERT, UPDATE, and DELETE operations because the index needs to be updated. Balance the use of indexes based on query patterns and performance needs.
  2. Choose Columns Wisely: Create indexes on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses.
  3. Monitor Performance: Regularly review and monitor index usage to ensure they are providing the intended performance benefits.

Frequently Asked Questions

Q1: Can an index be created on multiple columns?
A1: Yes, an index can be created on multiple columns, known as a composite index. This type of index is useful for queries that involve multiple columns.

Q2: How do I know if an index is being used by a query?
A2: You can use the EXPLAIN command to analyze a query’s execution plan and see if indexes are being utilized.

Q3: Can I create an index on a NULL column?
A3: Yes, you can create an index on columns that allow NULL values. The index will handle NULL values according to the database system’s indexing rules.

Q4: Can indexes be created on computed or virtual columns?
A4: Yes, many database systems support creating indexes on computed or virtual columns. The index will improve performance on queries involving these computed values.

Q5: How do I check which indexes are present on a table?
A5: Use system catalog queries or database management tools to list the indexes on a table. For example, in MySQL, you can use:

SHOW INDEX FROM table_name;

This command provides information about indexes and their columns.

❮ Previous
Next ❯
ads website