Working with Database Structures - Learning MySQL - page 205

A primary key uniquely identifies each row in a table. When you declare one to MySQL,
it creates a new file on disk that stores information about where the data from each row
in the table is stored. This information is called an index , and its purpose is to speed
up searches that use the primary key. For example, when you declare PRIMARY KEY
(artist_id) in the artist table in the music database, MySQL creates a structure that
allows it to find rows that match a specific artist_id (or a range of identifiers) extremely
quickly. This is very useful to match artists to albums, tracks, and playlist information.
You can display the indexes available on a table using the SHOW INDEX command:
mysql> SHOW INDEX FROM artist;
+--------+------------+----------+--------------+-------------+-----------+...
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |...
+--------+------------+----------+--------------+-------------+-----------+...
| artist | 0 | PRIMARY | 1 | artist_id | A |...
+--------+------------+----------+--------------+-------------+-----------+...
... +-------------+----------+--------+------+------------+---------+
... | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
... +-------------+----------+--------+------+------------+---------+
... | 6 | NULL | | | BTREE | |
... +-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
We've wrapped the output here so that it would fit on the page. The cardinality is the
number of unique values in the index; for an index on a primary key, this is the same
as the number of rows in the table.
Note that all columns that are part of a primary key must be declared as NOT NULL , since
they must have a value for the row to be valid. Without the index, the only way to find
rows in the table is to read each one from disk and check whether it matches the
artist_id you're searching for. For tables with many rows, this exhaustive, sequential
searching is extremely slow. However, you can't just index everything; we'll come back
to this point at the end of this section.
You can create other indexes on the data in a table. You do this so that other searches
—on other columns or combinations of columns—are extremely fast and in order to
avoid sequential scans. For example, suppose you often want to search by
artist_name . You can drop the table and modify the CREATE TABLE definition to add an
extra index:
mysql> DROP TABLE artist;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE artist (
-> artist_id SMALLINT(5) NOT NULL DEFAULT 0,
-> artist_name CHAR(128) DEFAULT NULL,
-> PRIMARY KEY (artist_id),
-> KEY artist_name (artist_name)
-> );
Query OK, 0 rows affected (0.06 sec)