Geek asks and answers

Can you have multiple keys in SQL and why do you need it?

Is there a reason you would like to have multiple KEYs in the table? What is the point of having multiple KEYs in the same table?

Here is an example 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),
)

In addition, you will notice that the database programmer does not do transaction_ida KEY. Is there a reason for this?

+3
source share
5 answers

KEYin MySQL, an alternative syntax for "index".
Indexes are common to databases, but at the moment they are not yet covered by ANSI - this is a pure miracle, similar to what they are. Typically, a table may have more than one index, since indexes improve data retrieval due to the speed of updating / deleting / inserting.

, MySQL (5.x?) , .

+4

.

  • ( WHERE KEY, )
  • ( UNIQUE )

.

+4

SQL PRIMARY KEY .

KEY(foo) - SQL. MySQL KEY foo - INDEX foo UNIQUE. ( , .)

UNIQUE INDICES, "-". ( INDEX, "" .) PRIMARY KEY - INDEX .

[] , , - , .

INDEX (INDEX foo, MySQL, KEY foo) - , . "" " "; , , . ( , FOREIGN KEYS .)

INDEX ( , "KEY"!) user_id () , :

... WHERE user_id = {somenumber}

INDEX FULL TABLE SCAN (, ).

, transaction_id , ( ) - , :

  • transaction_id ;
  • user_id = ... , INDEX. , , WHERE user_id = ... AND transaction_id = ..., , , , transaction_id - SCAN, , . WHERE transaction_id = ... FULL TABLE SCAN.

, EXPLAIN - - , MySQL, . , , .

.

+3

"" :

, . .

0

- , . relvar ( SQL-) -. , , , ( ;)

MySQL KEY, INDEX. , MySQL , .

SQL DDL , , , . transaction_id , , . , , , , user_id payment_time . , transaction_id , . , transaction_id .

0

More articles:


All Articles