How to Create Indexes in Microsoft Access on Windows 11
Posted on 19th June 2023
Microsoft Access is a relational database management system (RDBMS) that supports the use of indexes to improve the performance of queries. An index is a data structure (most often a B-tree) that stores the values of one or more columns of a table, and the rowids of the rows in which they appear.
Indexes are used to quickly locate rows based on the values in the index columns. Indexes can be created on any column or combination of columns in a table, and the index key can be up to 16 columns. Indexes can also be created on expressions.
Creating an Index
To create an index, you use the CREATE INDEX statement. The CREATE INDEX statement has the following syntax:
CREATE INDEX index_name
ON table_name (column_name1, column_name2, ...);
In this syntax, the index_name is the name of the index to be created and table_name is the name of the table on which the index is to be created. The column_name1, column_name2, … are the columns that you want to include in the index.
Suppose you want to create an index named idx_last_name on the last_name column of the customers table. The following statement creates the index:
CREATE INDEX idx_last_name
ON customers (last_name);
After the index is created, you can check it by looking at the table’s indexes:
SHOW INDEXES FROM customers;
The output of the statement is as follows:
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| customers | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
| customers | 1 | last_name | 1 | last_name | A | 5 | NULL | NULL | | BTREE | | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
As you can see from the output, the index has the name idx_last_name, it is located on the last_name column, and it is of the BTREE type.
Types of Indexes
Microsoft Access supports two types of indexes:
-
Primary Key Index – A primary key index is created on a column or combination of columns that uniquely identify a row in a table. A primary key index is always a unique index. You can create a primary key index by using the PRIMARY KEY constraint when you create a table. If you do not specify a primary key when you create a table, you can create a primary key index by using the CREATE INDEX statement and specifying the UNIQUE keyword.
-
Unique Index – A unique index is an index that enforces the uniqueness of the values in the index key. A unique index can be created by using the UNIQUE constraint when you create a table. If you do not specify a unique constraint when you create a table, you can create a unique index by using the CREATE INDEX statement and specifying the UNIQUE keyword.
Advantages of Indexes
Indexes have the following advantages:
-
Improved performance – Indexes can improve the performance of queries because the database server can use the index to quickly locate the rows that satisfy the query conditions.
-
Enforced uniqueness – Indexes can be used to enforce the uniqueness of the values in a column or combination of columns. This is useful if you want to ensure that no two rows in a table have the same values in a particular column or combination of columns.
Disadvantages of Indexes
Indexes have the following disadvantages:
-
Storage space – Indexes require storage space. The amount of space required depends on the number of rows in the table, the number of columns in the index, and the index type.
-
Increased complexity – Indexes can make some operations, such as data modification, slower because the database server must update the indexes as well as the data in the table.
Creating an Index on an Expression
An index can be created on an expression. An expression is a combination of one or more columns and operators that evaluates to a value. For example, the following expression evaluates to the concatenated first and last names of customers:
first_name || ' ' || last_name
</code
The following statement creates an index on the expression:
CREATE INDEX idx_name
ON customers (first_name || ' ' || last_name);
Creating a Composite Index
A composite index is an index on two or more columns. The following statement creates a composite index on the first_name and last_name columns:
CREATE INDEX idx_name
ON customers (first_name, last_name);
Creating a Partial Index
A partial index is an index on a subset of the rows in a table. The subset of rows is defined by a condition. Partial indexes are useful for indexing large tables where not all rows need to be indexed.
The following statement creates a partial index on the customers table. The index includes only rows where the first_name column begins with the letter A:
CREATE INDEX idx_name
ON customers (first_name)
WHERE first_name LIKE 'A%';
Creating a Multicolumn Index
A multicolumn index is an index on two or more columns. The order of the columns in the index is important. The following statement creates a multicolumn index on the last_name and first_name columns:
CREATE INDEX idx_name
ON customers (last_name, first_name);
The order of the columns in the index is important because the index is sorted first on the last_name column and then on the first_name column.
Creating a Spatial Index
A spatial index is an index on a column that contains spatial data. Spatial data is data that represents objects in a geometric space. The most common type of spatial data is points, which are represented by coordinates in a two-dimensional space.
The following statement creates a spatial index on the location column of the customers table:
CREATE INDEX idx_location
ON customers (location);
Creating an Index on a View
An index can be created on a view. The index is stored in the database and is used to improve the performance of queries on the view. The index is not visible to users and does not need to be created on the underlying table.
The following statement creates an index on the last_name column of the v_customers view:
CREATE INDEX idx_last_name
ON v_customers (last_name);
Creating a Unique Index
A unique index is an index that enforces the uniqueness of the values in the index key. A unique index can be created by using the UNIQUE constraint when you create a table. If you do not specify a unique constraint when you create a table, you can create a unique index by using the CREATE INDEX statement and specifying the UNIQUE keyword.
The following statement creates a unique index on the last_name column of the customers table:
CREATE INDEX idx_last_name
ON customers (last_name)
UNIQUE;
