Create Index on Column in Table

Using the BasicTable used earlier in this module as an example, a standard request would be to query this database by last name. If that's the case, you will want to consider creating an index on the last name so the SQL engine will automatically keep track of the last names and be able to retrieve them quickly. Creating an index is simple and straightforward. As with the CREATE TABLE statement from the prior lesson, you use the CREATE statement, but with the INDEX keyword:

CREATE INDEX index_name
ON table_name (column_name)

So, for the table you created above, with the first and last name fields and so on, if you want to create an index on the Lastname column, the statement would be as follows:

CREATE INDEX LastnameIndex
ON BasicTable (Lastname)

This creates the index, names it LastnameIndex, and sets it up on BasicTable. The information in the index is created initially, then maintained automatically as rows are inserted, deleted, or updated. Sometimes, the syntax you use to create an index can vary.

Creating indexes will likely not be something that you do directly in your queries against the database. You will likely need to work with a developer, DBA, or other system administration personnel to implement a new index. Still, it is important to understand what indexes are and the fact that they are available in the first place as a line of defense. The syntax, or how indexes are implemented, varies from database engine to database engine. For example, on some databases, the index may be stored separately from the data in your system. Other systems store the index as part of the data in your system. There may be considerations of disk space as well. Keep in mind that, if you start indexing every possible column, the disk space required to keep up with that index may be a problem on your servers.

• SQL CREATE INDEX Statement: The CREATE INDEX statement is used to create indexes in tables. Indexes allow the database application to find data fast; without reading the whole table.
• Table Index: An index can be created in a table to find data more quickly and efficiently. The users cannot see the indexes, they are just used to speed up searches and queries. Updating a table with indexes takes more time than updating a table without because the indexes also require an update. Therefore, you should only create indexes on columns and tables that will be frequently searched against.

Database indexes are important because they improve the speed and efficiency of query operations. Here’s why they are essential:

1. Faster Query Performance: Indexes allow databases to locate and retrieve data much more quickly, avoiding full table scans. When you query a large table without an index, the database has to check every row (a sequential search), which is slow. An index helps the database quickly pinpoint the relevant rows.
2. Efficient Data Sorting and Searching: Indexes store the indexed columns in a sorted order. This improves the performance of sorting operations (`ORDER BY` clauses) and allows for faster searching within a defined range (`BETWEEN` or `LIKE` queries).
3. Reduces I/O Load: By limiting the number of data pages that need to be read from disk, indexes reduce the input/output (I/O) load on the system, leading to better performance.
4. Improves JOIN Operations: Indexes make JOIN operations between tables faster. When two tables are joined on indexed columns, the database can efficiently match the rows using the index rather than comparing every row in both tables.
5. Unique Constraints: Indexes are used to enforce unique constraints, ensuring that certain columns (like primary keys) have unique values across all rows.

However, there are some trade-offs:

• Storage Overhead: Indexes require additional storage space.
• Slower Writes: When data is inserted or updated, the database needs to update the indexes as well, which can slow down write operations.

Indexes are a powerful tool for optimizing database performance, particularly for read-heavy workloads.
In some cases, you can expect an increase in the timing of your queries. It is important to place the information in your database, and then determine how you are most likely to query it. Only then can you really determine what columns need to be indexed. Note that while indexes are helpful for performance in most cases, adding indexes should only be done in those cases where you feel it is necessary. By adding too many indexes, you can begin to impact the performance of your system negatively because the engine will be updating the indexes constantly as your data changes.

Click on the Exercise link below to practice creating an index in SQL.
Creating Index - Exercise