You've looked at how to apply keys and whether you should. You can use a key to make lookups more
efficient, to help performance when you are selecting unique results, or when sorting or grouping data.
However, you have also seen that the unnecessary use of keys in frequently updated tables may
degrade performance.
Unique keys are used not only for performance but also to preserve the integrity of the data. Unique
keys can be formed out of the data itself or created as synthetic keys, independent of the data content.
MySQL gives you the AUTO_INCREMENT option when defining a numeric column, which can be
conveniently used to create unique synthetic keys.
You've seen how primary keys are a special type of unique key, essential in relating all relevant tables.
They must be unique and must never be null, thus guaranteeing that no row of data can ever fail to be
referenced through its primary key.
Q&A
Q: When do I need to be concerned about locks?
A:
Locks are important in a multithreaded environment, in which some threads
have the job of updating, inserting to, or deleting from the database. If there
are other threads concerned with reading from the database that may be
doing so simultaneously, you should consider using locks.Essentially, if a thread needs to use several lines of SQL to perform its
task, you must consider using a lock to preserve data integrity.
However, there may also be performance reasons why you would want
to use locks.
Q: What's the point of creating a non-unique key?
A:
Non-unique keys can improve database performance during read
operations. Although the resultant rows may not be uniquely identified by
the key, access to them and sorting and grouping of the result will be much
faster.
Q: Do all unique keys have to be primary keys?
A:
No. MySQL permits only one primary key per table, but there may be a
number of unique keys. Both unique keys and primary keys can speed up
the selecting of data with a WHERE clause, but a column should be chosen
as the primary key if this is the column by which you want to join the table
with other tables.
Q: Should every table have a primary key?
A:
Not necessarily. In general, tables should have a primary key whenever you
want to use them in a join with another table. However, you may not want to
do this with every table, and there are circumstances when it's better not to
use a key.
Q: I'm confused about when to use keys, and when not to. How do I
decide?
A:
Although the need for a primary key is easy to define, the use of other keys
may not be. There may be factors both for and against the use of keys and,
in some circumstances, they may be contradictory. In this case, the
database should be run with the keys and its performance measured. The
keys can then be dropped and the performance measured again. Then, re-
create the keys, perhaps in a different way, and keep experimenting until
performance is optimized. If need be, the database can be run with a view to
regularly dropping certain keys and creating them again, such as around the
time of a series of write operations.Exercises
- Imagine that you have a table called Orders, and you want to update it while no
other threads can read from it. You also have a table called Products, which you
want to read from but not update. Write the syntax for appropriate locking and
unlocking of these tables. - Consider each of the following scenarios. In which would you consider applying a key
to the table? Would you use a unique or non-unique key?
a. A table containing the birthdays of all of the people you know. Your
computer accesses it once a day to see if it's anyone's birthday today
and, if so, emails him or her a greeting.