Understanding Keys and Indexes

The terms keys and indexes are often used interchangeably. A key column stores values that are used to filter rows in the table, either in the WHERE clause when querying that table or as part of a join to indicate the relationship between two tables.

A key column in MariaDB is always indexed, but you can create an index on any table column or on multiple columns.

Primary Keys

Each table can contain only one primary key, which is a unique identifier for each row in the table. Often the primary key is an AUTO_INCREMENT column so that a new unique number is assigned automatically for each row added to the table.

You can be sure that when you query a table using a filter on a primary key field, MariaDB will return only one row for each match in the filter.

An incremental ID number makes a good primary key. A unique character codesuch as product_code in the sample products tablecan work well, too, as long as you can ensure that the value will always be unique.

You should not use a person's name as a primary key because there is always a chance, however rare, that you could get a duplicate record. If you attempt to insert a duplicate value into a primary key column, a key violation occurs and MariaDB gives an error.

Foreign Keys

A foreign key is a column that another table references. When you join two tables, the join condition usually is that the primary key from the first table must be equal to a foreign key in the second.

In the sample database, the foreign keys have the same name as the corresponding primary keys in their related tables. For instance, products. product_code is a primary key that is referenced by order_lines.product_codeeach line of an order refers to exactly one product using a unique identifier for the product.

Foreign Key Constraints

If you use the InnoDB table handler, you can add foreign key constraints at the database level to maintain referential integrity. You will learn how to do this in the section "Foreign Key Constraints with InnoDB," later in this chapter.

Indexes

Although there can be only one primary key on a table, you can create indexes on other columns in a table for performance reasons.

When MariaDB searches for a value in a table column, if there is no index, it must start with the first row and check each row until it reaches the end of the table. However, when an index is available, the process is much quicker: The index tells MariaDB where to look within the table for rows corresponding to that key value.

The more rows there are in a table, the bigger the impact a key can have on queries that return only a small number of rows from that table.

Selecting a Large Number of Rows

If a query is intended to return the majority of the rows in a table, an index is not necessary and can often slow down a query.

You can define a column as a UNIQUE KEY if you are sure that its values will be unique. Queries using a UNIQUE KEY are faster than a non-unique key because MariaDB knows that only one matching row will be returned from the table.

Previous Page Next Page