Can you have multiple Keys in SQL and why would you want that?

Question

Is there a reason why you would want to have multiple KEYs in a TABLE? What is the point of having multiple KEYs in one table?

Here is an example that I found:

CREATE TABLE orders(
id INT UNSIGNED NOT NULL AUTO INCREMENT,
user_id INT UNSIGNED NOT NULL,
transaction_id VARCHAR(19) NOT NULL,
payment_status VARCHAR(15) NOT NULL,
payment_amount DECIMAL(15) NOT NULL,
payment_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(id),
KEY(user_id),
)

Also, you'll notice the DBase programmer doesn't make transaction_id a KEY. Is there a reason for this?

Answer 1

KEY in MySQL is an alternate syntax for "index".
Indexes are common across databases, but they aren't covered by ANSI as of yet -- it's pure miracle things are as similar as they are. It can be common to have more than one index associated to a table -- because indexes improve data retrieval at the cost of update/delete/insert speed.

Be aware that MySQL (5.x?) automatically creates an index if one doesn't already exist for the primary key of a table.