This statement is used to create an “index” on a column in an existing table.
Key points on indexes:
- They are used to improve the efficiency of searches for data, presenting the data in a specific order, when joining tables (see the ultimate guide to
JOINstatements) and more.
- An index is a “system” object, meaning that it is used by the database manager.
- Part of this usage is for the database manager to update the index when the data used by the index changes in the related table. Keep this in mind because as the number of indexes increase in a database overall system performance can be impacted.
- If you find that your SQLs are running slow on a given table or tables, creating an index is the first thing to consider to correct the issue.
Here’s an example of the syntax of the
create index statement. Note that the syntax allows for an index to be over more than one column:
CREATE INDEX index_name ON table_name (column1, column2, ...);
To create a new index on the student table's field,
programOfStudy, use the following statement:
Here’s a statement to create the index:
create index pStudyIndex on student (programOfStudy);
In MySQL, you use the
ALTER TABLE command to change and drop indexes. MySQL Workbench also provides GUI tools to manage indexes.
But this is just scratching the surface. Check out the documentation for your database manager of choice and have fun trying different options yourself.