Creating Indexes in sql
In SQL database management systems, indexes are a popular feature used to optimize, improve, or speed up the performance of database table data queries. This is a helpful process in secondary storage database optimization, arranging or managing stored table data in a proper order and storing it sequentially for efficient read and write data operations. Indexing database table data speeds up the data query storage and retrieval process or performance. The indexing process allows the database to immediately find table rows instead of scanning the entire table.
In SQL database management systems, database users can create a new database table index by applying the CREATE INDEX command or statement.
Syntax for index creation in SQL databases.
CREATE INDEX index_name
ON table_name (column1, column2, …);
Elements of CREATE INDEX.
- index_name – This is the name of the newly created index, which the database user provides. A meaningful name should be provided for the new index, usually including the table name and table columns.
- table_name – This is the name of the table on which the new index is being created during the indexing process.
- (column1, column2, …) – These are the table columns in the database table that are being indexed. Database users can index and manage one or more columns as needed.
Example of a simple index in an SQL database.
Let’s assume the database user has a database table named “employee,” and the user frequently runs table queries that filter and display table record data based on employee_id. To speed up these database queries, the user can create an index on the employee_id column.
CREATE INDEX indx_employee_id
ON employee (employee_id);
In this simple index example.
- indx_employee_id is a user-defined index name. Here, it is created on the employee_id column of the new index employee table.
- Here’s how a database user can query it.
SELECT * FROM employee WHERE employee_id = 101;
Here, instead of scanning the entire database table, this query can immediately find the table row with employee_id = 101 by using the index.
Example of creating a multi-column index in a database table.
- If a database user needs to generate a query using multiple table columns, the user can create a new table column index on more than one table column. This is known as a composite index or multi-column index.
- For example, if the database user frequently runs new queries on a combination of the f_name and l_name columns in the employee table:
CREATE INDEX indx_name
ON employee (f_name, l_name);
Here’s this query statement:
SELECT * FROM employee
WHERE f_name = ‘Siddhi’ AND l_name = ‘Deora’;
Creating a column index in the employee table allows the database user to immediately find matching table rows based on both table columns by using the index.
Unique Index Example in SQL Database.
A newly created unique index in a database table helps ensure that all values in the indexed database table column are unique and distinct. Unique indexes are mostly used to enforce table data integrity. For example, if the email or employee ID column of every employee in the employee table should be unique, then the database user can create and manage a unique index.
CREATE UNIQUE INDEX indx_e-mail
ON employee (e-mail);
Here, the process of adding duplicate emails to the employee table will be blocked because uniqueness is enforced on the email column in the index.
Example of indexing with CREATE INDEX and WHERE (Partial Index).
In SQL database management, some database systems support the concept of partial indexes, which index only those table rows in a database table that satisfy a particular condition. This can be useful for database users when they have a large database table but only need to optimize or manage table queries for a specific subset of table rows.
For example, if a database user mostly creates queries for active employees (i.e., where the status column is ‘active’), they can create a partial index query like this:
CREATE INDEX indx_active_employee
ON employee (employee_id)
WHERE status = ‘active’;
This new index will only include those table rows where the status column is ‘active’, making it more space-efficient and faster for those specific table queries.
Example of an index for full-text search in an SQL database.
Many database software programs in SQL database management, such as MySQL, PostgreSQL, or SQL Server, support full-text index features for efficient text searching in text-based table columns. For example, in MySQL database software, database users can create full-text indexes on text or varchar table columns.
CREATE FULLTEXT INDEX indx_fulltext_description
ON product (description);
Here, database users can enable fast text searching for such database table queries by applying keywords like MATCH and AGAINST in the index.
SELECT * FROM product
WHERE MATCH(description) AGAINST (‘Macbook’);
Here, the full-text index improves searches that look for specific words or sentences within large text table fields.
Example of an index on a foreign key column in a database table.
- Indexes are mostly created automatically on foreign key table columns to increase referential integrity in database tables and improve the performance of table queries. However, database users can also create indexes on foreign key columns in a clear order.
- For example, here the employee table has a foreign key table column field named department_id that refers to or indicates the department table.
CREATE INDEX indx_department_id
ON employee (department_id);
Here, the index in the employee database table speeds up or improves queries like this.
SELECT * FROM employee
WHERE department_id = 102;
Creating indexes in SQL Server software: Clustered vs. Non-Clustered Indexes.
- There are mainly two types of index behaviour in SQL Server software.
- Clustered Index – These sorts the data rows of the index table in the database table based on the indexed table column field and stores the data in a secondary storage location. Each database table can have only one clustered index.
- Non-clustered index – This creates a separate structure from the index data rows in the database table, and it contains pointers to the real-time table data. Multiple non-clustered table indexes can be created in a database table as needed.
Example of a clustered index in an SQL database table.
Here, to create a clustered index on the employee_id table column field in the employee table, this column field is treated as a primary key.
CREATE CLUSTERED INDEX indx_employee_id
ON employee (employee_id);
Example of a non-clustered index in an SQL database table.
Here, to create a non-clustered index on the l_name table column field in the employee table.
CREATE NONCLUSTERED INDEX indx_l_name
ON employee (l_name);
Conclusion on Creating Indexes in SQL.
Creating indexes in SQL database tables is a best practice or method in database table query optimization, as it allows database users to perform read operations such as fast information searching, table record sorting, and multiple table joining in table queries immediately. However, there are also some side effects associated with the index creation process. For example, each index requires additional secondary storage for database table queries and can lead to slower data write operations. For best database query performance, it is important to create indexes on the most frequently queried columns in a selective order and to monitor and maintain them regularly.
