MySQL optimizer-MySQL hint

Force index forced Indexes

use only the indexes that are built on field1 , not the indexes on other fields.

SELECT * FROM table1 Force INDEX (field1)

IGNORE Index ignores indexes

Indexes on Field1 and field2 in table1 tables are not used

SELECT * FROM table1 IGNORE INDEX (field1, Field2)

Sql_no_cache do not cache the result set, turn off query buffering

Have some SQL statements need to query the data in real time, or not often ( Maybe one or two times a day ), so you need to turn the buffer off , regardless of the if the SQL statement is executed, the server does not look in the buffer and executes it every time.

SELECT Sql_no_cache table1.field1 from table1 as table1 LIMIT 10

Sql_cache forced query buffering

if the query_cache_type in My.ini is set to 2, it is only used Sql_cache query buffering is used only after the

SELECT Sql_calhe * FROM table1;

High_priority Priority Operation

High_priority can be used in select and insert operations to let MYSQL know that this operation takes precedence.

SELECT high_priority * FROM table1;

Low_priority hysteresis Operation

Low_priority can be used in the insert and update operations to let MySQL know that this operation is lagging.

Update low_priority table1 set field1=1_new where field1=1

Insert DELAYED delay insertion

INSERT DELAYED into, is the client submits data to MySQL, andmysql returns OK status to the client. Instead of inserting data into a table, it is stored in memory waiting to be queued. When MySQL is free, insert again. Another important benefit is that insertions from many clients are lumped together and written into a block. This is much faster than performing many separate inserts. The downside is that you can't return an auto-incrementing ID, and when the system crashes,MySQL hasn't had time to insert the data, and that data will be lost.

INSERT DELAYED into table1 set field1=1

Straight_join Force Connection Order , do not change by SQL Association Order

SELECT table1.field1,table2.field2 from table1 as Table1 left joins Table2 as table2 on table1.id = Table2.tb1_pkid LIMIT 1 00;

SELECT Straight_join table1.field1,table2.field2 from table1 as Table1 left joins Table2 as table2 on table1.id = TABLE2.TB 1_pkid LIMIT 100;

Sql_buffer_result forcing the use of temporary tables

when the data in the result set of our query is relatively long, we can Sql_buffer_result. option forces the result set to be placed in a temporary table so that you can quickly release MySQL the table lock ( so that other SQL statements can be queried for these records. ) And can provide a large recordset for the client for a long time.

SELECT Sql_buffer_result * from Field1 WHERE field1=1

Sql_big_result and sql_small_result grouping using temporal tables

generally used for grouping or DISTINCT keyword, this option notifies MySQLthat if necessary, the query results are placed in a temporary table and even sorted in a temporary table. sql_small_result is similar to Sql_big_result and seldom used.

SELECT Sql_buffer_result FIELD1, COUNT (*) from table1 GROUP by field1;

MySQL optimizer-MySQL hint