Learning Mysql

A primary key uniquely identifies each row in a table. When you declare one to MySQL,
it creates anew 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 speedup 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 inset 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 comeback 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 (

Download 4.24 Mb.

Share with your friends: