Monty says MySQL optimization (7)

This article is Monty's speech at the o'reilly Open Source Convention 2000 conference, mySQL-related transactions, general tips for using REPLACE, MySQL, benefits of using a MySQL higher version, and important features under active development are described in the MySQL optimization documentation.

,Transaction example

How to process transactions in the MyIASM table:
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_previus_statement
Where customer_id = some_id;
Mysql> unlock tables;
How to perform transactions in a BDB table:
Mysql> begin work;
Mysql> select sum (value) from trans where customer_id = some_id;
Mysql> update customer set total_value = sum_from_previus_statement
Where customer_id = some_id;
Mysql> COMMIT;
Note that you can use the following statements to avoid transactions:
UPDATE customer SET value = value + new_value WHERE customer_id = some_id;

12th,Example of using REPLACE

REPLACE functions like INSERT. Except for an old record with the same value as the new record on a unique index, the old record is deleted before the new record is inserted. SELECT 1 FROM t1 WHERE key = # is not used #
IF found-row
Lock tables t1
Delete from t1 WHERE key1 = #
Insert into t1 VALUES (...)
Unlock tables t1;
ENDIF
Use
Replace into t1 VALUES (...)

33,General skills

Use a short primary key. Use numbers instead of strings to join a table.
When using multiple key codes, the first part should be the most commonly used part.
If you have any questions, use more repeated columns for better key code compression.
If you run MySQL clients and servers on the same machine, use a socket instead of a TCP/IP address when connecting to MySQL (this improves performance by 7.5% ). You can do this by not specifying the host name or host name as localhost when connecting to the MySQL server.
If possible, use -- skip-locking (default on some operating systems), which will disable External locks and improve performance.
Use the hash value of the application layer instead of the long key code:
SELECT * FROM table_name WHERE hash = MD5 (concat (col1, col2) AND
Col_1 = 'constant' AND col_2 = 'constant'
Save the BLOB that needs to be accessed as a file in the file, and save only the file name in the database.
Deleting all rows is faster than deleting a majority of rows.
If SQL is not fast enough, study the underlying interfaces for accessing data.

34,Benefits from using MySQL 3.23

MyISAM: Portable big table format
HEAP: tables in memory
Berkeley DB: a table that supports transactions.
Many restrictions
Dynamic Character Set
More STATUS variables
CHECK and REPAIR tables
Faster group by and DISTINCT
Optimization of left join... IF NULL
Create table... SELECT
Create temporary table_name (...)
Automatic conversion from temporary HEAP table to MyISAM table
Copy
Mysqlhotcopy script

35,Important functions under active development

Improve Transaction Processing
Failed and secure Replication
Body Search
Delete multiple tables (then update multiple tables)
Better key code caching
Atomic RENAME (rename table foo as foo_old, foo_new as foo)
Query high-speed cache
MERGE TABLES
A better GUI client program