Some optimization methods of MySQL

Optimization of 1.Max

If there is no index, Max needs to retrieve all the rows to produce the results, which can be optimized by indexing:

CREATE INDEX  on products (price); Index the price in the Products table

In this way, querying the maximum value of price can quickly produce results.

2. Optimization of sub-queries

The following sub-query:

SELECT  from WHERE inch (SELECT from vendors);

You can use the Join method to optimize:

SELECT  from INNER JOIN  on = vendors.prod_id;

However, if the vendors contains duplicate prod_id, the duplicate query results are returned. Therefore, the DISTINCT keyword needs to be added.

Optimization of 3.Limit

Avoid using limit 500, 5, and so on, because 505 rows will be queried. When the primary key (for example, ID) Order increases, you can sort by the primary key and limit it to id>500 and <=505, which will only query for the 5 rows you want.

4. Horizontal Split and vertical split

Horizontal splitting means that data is stored in a table that is divided into several structures of the same number of fields, thus improving the efficiency of finding. Vertical splitting is the splitting of several columns of a table to form a new table, avoiding too many columns in the table.

Some optimization methods of MySQL