Four, MySQL optimized--sql statement optimization tips

1. Insert data in large quantities

(1) for MyISAM:

ALTER TABLE table_name disable keys;

Loading data;

ALTER TABLE TABLE_NAME enables keys;

(2) for InnoDB:

(a) The data to be imported will be sorted by primary key;

(b), set unique_checks = 0, turn off the uniqueness check;

(c), set autocommit=0, turn off auto-commit;

What is the difference between 2,myisam and InnoDB?

(1) MyISAM does not support foreign keys, INNODB support.

(2) MyISAM does not support transactions, INNODB support.

(3) The data is stored in different ways (if the storage engine is MyISAM, create a table that corresponds to three files *.) myd,*. myi,*.frm; If InnoDB, there is only one file *.frm, the data is stored in ibdata1).

Note: For the storage engine is a MyISAM database, if you often do delete and modify the record operation, you need to periodically execute "optimize table name", the data table is defragmented.

3, optimizing the GROUP BY statement

By default, MySQL sorts all group by Col1,col2. This is similar to specifying an order by Col1,col2 in a query. If the query includes group by but the user wants to avoid the consumption of sorting results, you can use ORDER by NULL to suppress sorting. For example: SELECT * FROM Dept GROUP by ename ORDER by NULL;

4, in some cases, you can use a connection instead of a subquery

Because you use Join,mysql, you do not need to create temporary tables in memory.

5, if you want to use an index in a query statement that has or, you must use an index for each condition between or, and if you do not have an index, you should consider increasing the index.

6, in applications with high precision, it is recommended to use fixed-point numbers (decimal) to store values to ensure the accuracy of the results. Use less floating-point float.

CREATE TABLE SA1 (T1 float (10,2));

CREATE TABLE SA2 (T1 decimal (10,2));

INSERT into SA1 values (1000000.32);

INSERT into SA2 values (1000000.32);

SELECT * from SA1;

SELECT * from SA2;

7, Saada

Four, MySQL optimized--sql statement optimization tips