Ways to optimize MySQL database

1, choose the most applicable field properties, as far as possible to reduce the definition of field length, as far as possible to set the field not NULL, such as ' Province, gender ', preferably set to enum
2. Use connection (join) instead of subquery:
A. Delete no orders customer: Delete from CustomerInfo WHERE customerid Not IN (SELECT CustomerID from OrderInfo)
B. Extracting all Customers without orders: Select from CustomerInfo WHERE CustomerID Not IN (SELECT CustomerID from OrderInfo)
c. Improved speed optimization for B: SELECT from CustomerInfo left JOIN OrderID Customerinfo.customerid=orderinfo.customerid
WHERE Orderinfo.customerid is NULL
3. Use Union (Union) instead of manually created temporary table
A. Creating a temporary table: select name from ' nametest ' UNION select username from ' Nametest2 '
4. Transaction processing:
A. Ensure data integrity, such as additions and modifications, both are executed and accesses than either failures fail
mysql_query ("BEGIN");
mysql_query ("INSERT into CustomerInfo (name) VALUES (' $name 1 ')";
mysql_query ("select * from ' OrderInfo ' where customerid=". $id ");
mysql_query ("COMMIT");
5, lock the table, optimize transaction processing:
A. We use a SELECT statement to remove the initial data and, with some calculations, update the new value to the table with the UPDATE statement.
a LOCK TABLE statement with the WRITE keyword guarantees that the UNLOCK TABLES command is executed before the
There are no other accesses to insert, UPDATE, or delete inventory .
mysql_query ("LOCK TABLE customerinfo READ, OrderInfo WRITE");
mysql_query ("SELECT CustomerID from ' CustomerInfo ' where id=". $id);
mysql_query ("UPDATE ' orderinfo ' SET ordertitle= ' $title ' where customerid=". $id);
mysql_query ("UNLOCK TABLES");
6, using foreign keys, optimize the locking table
A. Map the CustomerID in the CustomerInfo to the CustomerID in OrderInfo,
any record of no legal CustomerID will not be written in OrderInfo .
CREATE TABLE CustomerInfo
         (
CustomerID INT not NULL,
PRIMARY KEY (customerid)
) TYPE = INNODB;
CREATE TABLE OrderInfo
         (
OrderID INT not NULL,
CustomerID INT not NULL,
PRIMARY KEY (customerid,orderid),
FOREIGN KEY (customerid) REFERENCES CustomerInfo
(CustomerID) on DELETE CASCADE
) TYPE = INNODB;
Note: ' On delete CASCADE ', this parameter guarantees that when a record in the CustomerInfo table is deleted, the order is also deleted
all records for that user in the table, note that using foreign keys to define the transaction security type is InnoDB;
7. Build the index:
A. Format:
(normal index)
created: Create INDEX < index name > on tablename (indexed field)
modified: Alter TABLE tablename ADD index [index name] (indexed field)
Genesis specified index: CREATE TABLE tablename ([...],index[index name] (index field))
(unique index)
created: Create UNIQUE < index name > on tablename (indexed field)
modified: Alter TABLE tablename ADD UNIQUE [index name] (indexed field)
Genesis specified index: CREATE TABLE tablename ([...],unique[index name] (index field))
(primary key)
It is a unique index, generally in the creation of tables is established, in the form of:
creata TABLE tablename ([...],primary key[index field])
8. Optimizing Query Statements
A. It's a good idea to compare operations in the same field and minimize function operations on established index fields
Example 1:
SELECT * FROM order WHERE year (orderDate) <2008; (slow)
SELECT * from order WHERE orderdate< "2008-01-01";(fast)
Example 2:
SELECT * FROM Order WHERE addtime/7<24; (slow)
SELECT * FROM Order WHERE addtime<24*7; (FAST)
Example 3:
SELECT * from the order WHERE title like "%good%";
SELECT * from order WHERE title>= "good" and name< "good";

Ways to optimize MySQL database