Creating a MySQL Index - New Table

If you are creating a new MySQL table you can specify a column to index by using the INDEX term as we have below. We have created two fields: name and employeeID (index).

MySQL Code:

CREATE TABLE employee_records (
 name VARCHAR(50), 
 employeeID INT, INDEX (employeeID)
)

Creating a MySQL Index - Existing Table

You can also add an index to an older table that you think would benefit from some indexing. The syntax is very similar to creating an index in a new table. First, let's create the table.

MySQL Code:

CREATE TABLE employee_records2 (name VARCHAR(50), employeeID INT)

With our newly created table we are going to update the "employee_records2" table to include an index.

MySQL Code:

CREATE INDEX id_index ON employee_records2(employeeID)

We keep our existing employeeID field and create a new index id_index that is made up of employeeID data.