way to refer to each entry in the table. A synthetic key for each customer will never change, irrespective
of what other changes happen to customers as individuals.
To create a synthetic customer_number field to be the primary key for your customers, you can define
it as follows: CREATE TABLE customers (customer_number INT(8) PRIMARY KEY NOT NULL,
last_name VARCHAR(30), ...)This tells MySQL to create a primary key of type integer, with 8 digits (enough for 100,000,000
customers!).Choosing keys is no trivial matter. After you decide you need a primary key in a table, it may be
tempting to create synthetic keys all over the place. Should you do this?Before deciding to create a synthetic key, you should be sure that you've looked hard enough for some
other naturally unique identifier. Otherwise, you risk complicating the design and slowing down the
performance of your database without reason.Key Choices
When should you use a key? What kind of key should you use? Using keys appropriately in your database
design can improve both design and performance. But making the wrong choices may work against you.
The following sections provide a brief guide to using and choosing keys.When to Use Keys
Whether to use a key on a column will depend on the types of queries you intend to carry out.
When to use keys:
WHERE clauses—If you are frequently using a column for selection criteria, a key on
this column will generally improve performance. The lower the number of rows in a
table that are likely to be returned by a SELECT...WHERE statement, the more a
key will be beneficial. In particular, when a query is expected to return a unique
result, a key should be created on the column or columns used by the WHERE
clause.
ORDER BY and GROUP BY clauses—Sorting data is a costly exercise. Because a key
automatically renders results in alphabetical order, columns that you want to ORDER
BY or GROUP BY are good candidates for keys.
MIN() and MAX()—Keys are highly efficient at finding minimum and maximum
values in a column.
Table joins—The use of keys will always help performance where the indexed
columns are being used to join tables. In general, most, if not all, columns that are at
some stage used in a table join should be indexed.When Not to Use Keys
Although keys can bring great benefits to SELECT operations, this can come at a price. Consider a few
instances in which you should avoid using keys.
Tables used in frequent write operations—Whenever you perform a write operation,
such as an INSERT, UPDATE, or DELETE on a table, both the main table and the key
have to be written to. A key thus presents an overhead. On a system where you are
performing frequent write operations, you must consider this overhead carefully and
try to balance it against the benefits you may get when you perform read operations.
Instances when a SELECT will return a high proportion of rows—You would not
bother to index the words he, she, or it in the index of a book. In the same way, you
would not want a key on "customer salutation" because WHERE conditions selecting
on Mr, Ms, and so on would return a high proportion of rows each time. Performance
would be degraded because each time a successful match is made, the database
has had to read both the index and the table data. A table scan would be faster than
performing two reads for such a high proportion of rows.
Small tables—There's little advantage to be gained creating keys on small tables; a
table scan may be just as fast.