JOIN is used to replace the actual operation process of subqueries (Sub-Queries). UNION is used to replace the manual creation of temporary tables.
MySQL database performance optimization 1. select the most suitable field attribute
MySQL can support access to large data volumes, but generally, the smaller the table in the database, the faster the query will be executed on it. Therefore, when creating a table, we can set the field width in the table as small as possible to achieve better performance.
For example, if you set it to CHAR (255) when defining the zip code field, it is obvious that unnecessary space is added to the database, and even the VARCHAR type is redundant, because CHAR (6) can well complete the task. Similarly, if possible, we should use MEDIUMINT instead of BIGIN to define integer fields.
Another way to improve efficiency is to set the field to not null whenever possible, so that the database does NOT need to compare NULL values during future queries.
For some text fields, such as "Province" or "gender", we can define them as the ENUM type. In MySQL, The ENUM type is processed as the numeric data, and the numeric data is processed much faster than the text type. In this way, we can improve the database performance.
MySQL database performance optimization 2. Use JOIN instead of Sub-Queries)
MySQL supports SQL subqueries from 4.1. This technique can use the SELECT statement to create a single column query result, and then use this result as a filter condition in another query. For example, if you want to delete a customer who has no orders in the basic customer information table, you can use the subquery to retrieve the customer IDs of all orders from the sales information table, then pass the result to the main query, as shown below:
- DELETE FROM customerinfo
- WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
Subqueries can be used to complete SQL operations that require multiple logical steps at a time. At the same time, transactions or tables can be prevented from being locked and can be easily written. However, in some cases, subqueries can be replaced by more efficient joins. For example, if we want to retrieve all users without order records, we can use the following query:
- SELECT * FROM customerinfo
- WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
If you use JOIN to complete this query, the speed will be much faster. Especially when the salesinfo table has an index on CustomerID, the performance will be better. The query is as follows:
- SELECT * FROM customerinfo
- LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
- CustomerID
- WHERE salesinfo.CustomerID IS NULL
JOIN is more efficient because MySQL does not need to create a temporary table in the memory to perform the query in two steps.
MySQL database performance optimization 3. Use UNION instead of manually created temporary tables
MySQL 4.0 and later versions support UNION queries. It can merge two or more SELECT queries in a temporary table. When the query Session on the client ends, the temporary table is automatically deleted to ensure the database is neat and efficient. When using UNION to create a query, we only need to use UNION as the keyword to connect multiple SELECT statements. Note that the number of fields in all SELECT statements must be the same. The following example demonstrates a query using UNION.
- SELECT Name, Phone FROM client
- UNION
- SELECT Name, BirthDate FROM author
- UNION
- SELECT Name, Supplier FROM product
The above content is a "Magic Hand" for] optimizing MySQL database performance. I hope it will help you in this regard.