MySQL: INDEX name (lastName, firstName)

Question

this is the query from tutorial i read

CREATE TABLE Employee (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
departmentId TINYINT UNSIGNED NOT NULL
    COMMENT "CONSTRAINT FOREIGN KEY (departmentId) REFERENCES Department(id)",
firstName VARCHAR(20) NOT NULL,
lastName VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
ext SMALLINT UNSIGNED NULL,
hireDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
leaveDate DATETIME NULL,
INDEX name (lastName, firstName),
INDEX (departmentId)
)

what is the function of INDEX name (lastName, firstName) ?

Please inform me if my question is not clear.

Thank you,
GusDe

Answer 1

INDEX name (lastName, firstName) is creating an additional index for fast lookups when you are querying using the lastname with or without the first.

It is a composite index because it includes two columns.

Added The author of the tutorial is "guessing" that employees will often be looked up by their name or by their departmentID. That's why he or she created the two additional indexes.

-- The primary key index is automatically created for you in most dbms systems.

In real life, it is not wise to solely rely on "guessing" what columns in the tables should be indexed. Instead, use the "slow queries" log (MySQL example) to determine what queries are executing slowly and how to speed them up. Usually the answer is to add another index or two.

ps. The downside of indexes is that they increase the time required to add, update or delete data in the table since the table and the index have to be modified. A second downside of indexes is that they take up room in the db. But storage is cheap these days.

Since most databases have far more reads than writes, the speedup in querying provided by the index usually far outweighs the costs.