optimization of Common SQL

Import data

For MyISAM storage engine tables, you can quickly import large amounts of data in the following ways

ALTER TABLE tbl_name disable keys; Turn off updates for non-unique indexes on tables

Loading the data

ALTER TABLE tbl_name enable keys;

If the table is empty, the default is to import the data before creating the index, so you don't have to set it up.

This approach does not improve the efficiency of importing data for INNODB types of tables, and there are several ways to

The representation of the 1.InnoDB type is saved in the order of the primary key, so the imported data is arranged in the order of the primary key, which effectively improves the efficiency of importing the data.

2. Execute set unique_checks=0 before importing data, turn off uniqueness Check, execute set unique_checks=1 after end, restore uniqueness check.

3. Execute set autocommit=0 before importing data, turn off Autocommit, execute set autocommit=1 after completion, and resume autocommit.

Optimizing INSERT Statements

If you insert many rows from the same customer at the same time, you should try to use the INSERT statement for more than one value table, faster than a single insert that is executed separately.

Minimize additional sorting and return ordered data directly through the index.

A subquery is less efficient than an associated query, because you do not create a temporary table in memory to complete this logical two-step query effort.

For example SELECT * from the customer where customer_id not in (select customer_id from Payment)

Replace with SELECT * FROM customer a LEFT join payment B to a.customer_id=b.customer_id where b.customer_id is null

Regular Expressions:

Select first_name, email from customer where email regexp "@163[,." com$ "

optimization of Common SQL