Home

Indexes

Whenever a query includes a WHERE clause or JOIN … ON condition, MySQL has to determine the subset of rows that meet the given criteria. Without an index, MySQL does this by scanning the entire table, top-to-bottom, to find the matching rows. This can be extremely slow. By adding an index on the columns that define the WHERE or ON criteria, MySQL can much more quickly identify matching rows. In computer science terms, the slow non-index approach is “linear time” (i.e., slow) while the indexed approach is either “logarithmic time” or “constant time” (i.e., much faster).

Showing, adding, removing indexes

SHOW INDEXES FROM tbl_name
ALTER TABLE tbl_name ADD PRIMARY KEY (col_name, ...)
ALTER TABLE tbl_name ADD UNIQUE (col_name, ...)
ALTER TABLE tbl_name ADD INDEX (col_name, ...)
ALTER TABLE tbl_name ADD FOREIGN KEY (col_name, ...) REFERENCES tbl_name (col_name, ...)
ALTER TABLE tbl_name DROP PRIMARY KEY
ALTER TABLE tbl_name DROP INDEX index_name
ALTER TABLE tbl_name DROP FOREIGN KEY index_name

“Show” output

Extra features for foreign keys

Foreign keys add constraints: you cannot add a row if the foreign key column does not point to a proper row in the other table, and you cannot remove a row in the other table if it is being pointed to via a foreign key.

However, MySQL supports “cascading” deletes so that if you delete a row pointing to a foreign key or a row being pointed at, the delete will propagate or cascade to the other table and delete all pointed-at/pointed-to rows as well. This ensures the constraint is still met.

ALTER TABLE tbl_name ADD FOREIGN KEY (col_name, ...) REFERENCES tbl_name (col_name, ...)
  ON DELETE CASCADE

You have other options as well:

  ON DELETE SET NULL
  ON DELETE RESTRICT -- same as normal case
CINF 201 material by Joshua Eckroth is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License. Source code for this website available at GitHub.