1. Select the most applicable field property MySQL can support the amount of data volume, but it generally said that the smaller the table in the database, the faster the query executed above it. Therefore, in order to create a table, in order to achieve better performance, we can set the width of the field in the table as small as possible. For example, when defining this field, if it is set to Char (255), it is clear that the database adds unnecessary space, and even the type of varchar is also redundant, because char (6) can be good Complete the task. Similarly, if possible, we should use MediumInt instead of Bigin to define a whole field. Another way to improve efficiency is in the case of possible, you should try to set the field to not NULL as much as possible, so that the database does not have to compare the NULL value when performing queries. For some text fields, such as "provinces" or "gender", we can define them as the ENUM type. Because in MySQL, the Enum type is treated as a numeric data, and the speed of the numerical data is processed much more than the text type. This way, we can improve the performance of the database. 2. Use the connection (Join) instead of sub-query (SUB-queries) MySQL from 4.1 Start support SQL subquery. This technology can use the SELECT statement to create a single column query result, and then use this result as filtering conditions in another query. For example, we have to delete the customer's basic information table without any order, you can use the sub-query first to take all the client IDs that make orders from the sales information table, and then pass the results to the primary query, as shown below :
Delete from CustomerInfowhere Customerid NOT IN (Select Customerid from SalesInfo)
Using subqueries can do a few logically a SQL operation that requires multiple steps that can be completed one-time. It can also avoid transactions or table locks, and it is easy to write. However, in some cases, subqueries can be connected to Join .. replacement. For example, suppose we have to take all users without order records, you can use the query below:
Select * from customerInfowhere Customerid Not in (Select Customerid from SalesInfo) If you use the connection (JOIN) .. to complete this query, the speed will be much faster. Especially when there is an index of Customerid in the SalesInfo table, performance will be better, the query is as follows:
Select * from customerInfo Left Join SalesInfoon CustomerInfo.customerid = SalesInfo. Customerid Where SalesInfo.customerid is NULL
