MySQL force operation and order by usage

MySQL force operation and order by usage

We use hint commonly used in MySQL for detailed parsing. If you use Oracle frequently, you may know that Oracle has many hincvt features and provides many methods to optimize SQL statements.

Similarly, similar hint functions are available in MySQL. The following describes some common examples.

Force index MySQL FORCE INDEX

1. SELECT * FROM TABLE1 force index (FIELD1 )...

The preceding SQL statement only uses indexes created on FIELD1, instead of indexes on other fields.

IGNORE the INDEX IGNORE INDEX

Note: If you use a forced index to hit a query with a long time span, the query efficiency will be higher.

1. SELECT * FROM TABLE1 ignore index (FIELD1, FIELD2 )...

In the preceding SQL statement, indexes on FIELD1 and FIELD2 in Table 1 are not used.

Disable Query Buffer SQL _NO_CACHE

1. SELECT SQL _NO_CACHE field1, field2 FROM TABLE1;

Some SQL statements need to query data in real time or are not frequently used (maybe one or two times a day). In this way, you need to disable the buffer, regardless of whether the SQL statement has been executed or not, the server does not search in the buffer. It will be executed every time.

MySQL force Index forced Index: Forced Query Buffer SQL _CACHE

1. SELECT SQL _CALHE * FROM TABLE1;

If query_cache_type in my. ini is set to 2, the query buffer is used only after SQL _CACHE is used.

HIGH_PRIORITY

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

1. SELECT HIGH_PRIORITY * FROM TABLE1;

Lagging operation LOW_PRIORITY

LOW_PRIORITY can be used in insert and update operations to let MySQL know that this operation lags behind.

1. Update LOW_PRIORITY table1 set field1 = where field1 =...

INSERT DELAYED

1. Insert delayed into table1 set field1 =...

Insert delayed into: the client submits data to MySQL, and MySQL returns OK to the client. This is not to insert data into the table, but to store the data in the memory and wait for the queue. When MySQL is free, insert it again. Another important benefit is that inserts from many clients are centralized and written into a block. This is much faster than executing many independent inserts. The disadvantage is that you cannot return an auto-incrementing ID, and if the system crashes, MySQL will lose the data before it can be inserted.

Force join order STRAIGHT_JOIN

1. SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE...

According to the preceding SQL statement, STRAIGHT_JOIN forces MySQL to connect tables in the order of TABLE1 and table2. If you think it is more efficient to connect in the order recommended by MySQL, you can use STRAIGHT_JOIN to determine the connection sequence.

MySQL force Index forced Index: forced use of temporary table SQL _BUFFER_RESULT

1. SELECT SQL _BUFFER_RESULT * FROM TABLE1 WHERE...

When there is a large amount of data in the query result set, you can use SQL _BUFFER_RESULT. option to force the result set to the temporary table, so that the MySQL table lock can be quickly released (so that other SQL statements can query these records ), it can also provide a large record set for the client for a long time.

Use the temporary tables SQL _BIG_RESULT and SQL _SMALL_RESULT for grouping.

1. SELECT SQL _BUFFER_RESULT FIELD1, COUNT (*) FROM TABLE1 GROUP BY FIELD1;

It is generally used for grouping or DISTINCT keywords. This option notifies MySQL that if necessary, the query results will be placed in the temporary table, or even sorted in the temporary table. SQL _SMALL_RESULT is rarely used compared to SQL _BIG_RESULT.

----------------------------------------------------------

The role of explain has been mentioned earlier.

1. When you query only one piece of data, add limit 1;

2. When you query a fixed number of data items, add the order by + field.

In this way, the cursor will not query all

Example:

The number of rows is 15729009.

If order by id is added

If row is 200, the cursor stops after 200.

This article permanently updates the link address: