INDEX

CREATE INDEX

The CREATE INDEX command establishes indexes within tables, facilitating efficient data retrieval (permitting duplicate values).

Indexes enhance database search and query performance, though they are not visible to users.

The subsequent SQL creates an index named “idx_lastname” on the “LastName” column within the “Persons” table:

CREATE INDEX idx_lastname
ON Persons (LastName);

When creating an index on a combination of columns, you can list the column names within the parentheses, separated by commas, as demonstrated below:

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

Please ensure to verify the syntax for creating indexes in your specific database, as it may vary between different database management systems.

Please note that updating a table with indexes requires more time compared to updating a table without indexes, as indexes also necessitate updating. Therefore, only create indexes on columns that will undergo frequent searches.

DROP INDEX

The DROP INDEX command is employed to remove an index from a table.

MS Access:

DROP INDEX index_name ON table_name;

SQL Server:

DROP INDEX table_name.index_name;

DB2/Oracle:

DROP INDEX index_name;

MySQL:

ALTER TABLE table_name
DROP INDEX index_name;