Eight methods to optimize MySQL database performance (2) _ MySQL

Eight methods for optimizing MySQL database performance (2) bitsCN.com

Create table customerinfo
(
CustomerID int not null,
Primary key (CustomerID)
) TYPE = INNODB;
Create table salesinfo
(
SalesID int not null,
CustomerID int not null,
Primary key (CustomerID, SalesID ),
Foreign key (CustomerID) REFERENCES customerinfo
(CustomerID) ON DELETECASCADE
) TYPE = INNODB;

Note the parameter "on delete cascade" in the example ". This parameter ensures that when a customer record in the customerinfo table is deleted, all records related to this customer in the salesinfo table will also be deleted automatically. If you want to use foreign keys in MySQL, remember to define the table type as the InnoDB type of the transaction security table when creating the table. This type is not the default MySQL table type. The defined method is to add TYPE = INNODB in the create table statement. As shown in the example.

7. use indexes

Index is a common method to improve database performance. it allows database servers to retrieve specific rows at a much faster speed than no index, especially when a query statement contains MAX (), when using the MIN () and ORDERBY commands, the performance improvement is more obvious. Which fields should be indexed? In general, the index should be built on the fields that will be used for JOIN, WHERE judgment and order by sorting. Try not to index a field in the database that contains a large number of repeated values. For an ENUM type field, it is very likely that a large number of repeated values appear, such as "province" in customerinfo ".. it is not helpful to create an index on such a field. On the contrary, it may also reduce the performance of the database. When creating a TABLE, we can CREATE an appropriate INDEX at the same time, or use alter table or create index to CREATE an INDEX later. In addition, MySQL

Full-text indexing and search are supported from version 3.23.23. Full-text index is a FULLTEXT index in MySQL, but it can only be used for tables of the MyISAM type. For a large database, it is very fast to load data to a TABLE without FULLTEXT indexes and then CREATE an INDEX using alter table or create index. However, if you load data to a table with a FULLTEXT index, the execution will be very slow.

8. optimized query statements

In most cases, using indexes can increase the query speed. However, if an SQL statement is improperly used, indexes cannot play its due role. The following are some notes. First, it is best to compare fields of the same type. Before MySQL 3.23, this is even a required condition. For example, you cannot compare an indexed INT field with a BIGINT field. However, in special cases, when a CHAR field is of the same size as a VARCHAR field, you can compare them. Second, do not use functions to operate indexed fields.

For example, when the YEAE () function is used on a DATE field, the index cannot play its due role. Therefore, although the two queries below return the same results, the latter is much faster than the former.

SELECT * FROM order where year (OrderDate) <2001;
SELECT * FROM order WHERE OrderDate <"2001-01-01 ";

In the same case, when the numeric field is calculated:

SELECT * FROM inventory WHERE Amount/7 <24;
SELECT * FROM inventory WHERE Amount <24*7;

The above two queries also return the same results, but the subsequent query will be much faster than the previous one. Third, when searching for a struct field, we sometimes use the LIKE keyword and Wildcard. Although this method is simple, it is at the cost of system performance. For example, the following query will compare each record in the table.

SELECT * FROM books
WHERE name like "MySQL %"

However, if the following query is used, the returned results are the same, but the speed is much faster:

SELECT * FROM books
WHERE name> = "MySQL" and name <"MySQM"

Finally, you should note that you should avoid making MySQL automatically convert the data type during the query, because the conversion process also makes the index ineffective.

BitsCN.com