The parameter "on delete cascade" in the note example. This parameter guarantees that all records related to the customer in the SalesInfo table are also automatically deleted when a customer record in the CustomerInfo table is deleted. If you want
Use the foreign key in MySQL, you must remember to define the type of the table as the transaction security table InnoDB type when you create a table. This type is not the default type of the MySQL table. The definition method is to add Type = InnoDB in the CREATE TABLE statement. As shown in example.
7, use indexes
Index is improved
Common methods for database performance, it can make the database
The server retrieves specific rows than if there is no index, especially when the query statement contains max (), min () and orderby, the performance is more obvious. Which fields should I establish an index? Generally speaking, the index should be built on fields that will be used for Join, WHERE judgment, and Order By. Try not to establish an index in a field in a database containing a large number of duplicate values. For a field of an enum type, there is a very possible situation, such as "province" in CustomerInfo .. field, establish an index on such a field will not help; the opposite, it is possible Reduce the performance of the database. We can create a suitable index at the same time when you create a table, or you can use Alter Table or CREATE INDEX to create an index later. In addition, mysql
Full-text indexing and search started from version 3.23.23. Full-text index is a FullText type index in MySQL, but can only be used for tables for Myisam types. For a large database, load the data into a table without a fultext index, then use the ALTER TABLE or CREATE INDEX to create an index, which will be very fast. However, if you load data into a table that already has a fultext index, the execution process will be very slow.
8, optimized query statement
In most cases, using an index can increase the speed of the query, but if the SQL statement is inappropriate, the index will not be able to play its own role. Below is a few aspects that should be noted. First, it is best to perform a comparison between the same type of field. Before MySQL 3.23, this is even a must-have condition. For example, it is not possible to compare an indexed INT field and a Bigint field; however, as a special case, when the field of the char type and the field size of the VARCHAR type field, they can be compared. Second, do not use the function to operate on the field of the indexed field.
For example, when using a Yeae () function on a Date type field, the index will not function. Therefore, the following two queries are the same as the result of returning, but the latter is much better than the former.
Select * from Order WHERE YEAR (ORDERDATE) <2001; SELECT * from Order Where OrderDate <"2001-01-01";
The same situation also occurs when the numeric field is calculated:
Select * from Inventory WHERE AMOUNT / 7 <24; SELECT * from Inventory Where Amount <24 * 7;
The above two queries are also returning the same result, but the back query will be much faster than the previous. Third, when searching the characters field, we sometimes use the LIKE keyword and wildcards, which is simple, but it is also expected to sacrifice system performance. For example, the following query will compare each record in the table.
Select * from bookswhere name like "mysql%"
But if you change the result of the following query, the result is the same, but the speed is going to be a lot: .. Select * from bookswhere name> = "mysql" and name <"mysqm"
Finally, you should pay attention to avoiding automatic type conversion in the query because the conversion process will make indexes do not work.
