MySQL Optimization (vii)

31. Examples of Business

How the Myiasm table handles transactions:

mysql> LOCK TABLES Trans READ, customer WRITE;

Mysql> select sum (value) from trans where customer_id=some_id;

Mysql> Update Customer Set Total_value=sum_from_previous_statement

where customer_id=some_id;

Mysql> UNLOCK TABLES;

BdB How the table is transacted:

Mysql> BEGIN WORK;

Mysql> select sum (value) from trans where customer_id=some_id;

Mysql> Update Customer Set Total_value=sum_from_previous_statement

where customer_id=some_id;

Mysql> COMMIT;

Note that you can bypass the transaction with the following statement:

UPDATE customer SET value=value+new_value WHERE customer_id=some_id;

32, use the example of replace

Replace functions like inserts, except that if an old record has the same value as the record on a unique index, the old record is deleted before the new record is inserted. Do not use SELECT 1 from T1 WHERE key=#

IF Found-row

LOCK TABLES T1

DELETE from T1 WHERE key1=#

INSERT into T1 VALUES (...)

UNLOCK TABLES T1;

ENDIF

And with

REPLACE into T1 VALUES (...)