Monty says MySQL optimization (6)

This article is Monty's speech at the o'reilly Open Source Convention 2000 conference, it involves MySQL to solve a query, MySQL is very good, MySQL should be avoided, MySQL various locking, MySQL more information five aspects, is a detailed MySQL optimization article

26,Know how MySQL solves a query

Run the item column command and try to understand its output:
Show variables;
Show columns from... G
Explain select... G
Flush status;
SELECT ...;
Show status;

27th,MySQL is very good

Logs
During many connections, the connection is very fast.
When both SELECT and INSERT are used.
When you choose not to combine updates with the time-consuming options.
When most selections/updates use a unique key code.
When multiple tables are not locked for a long time.
When using large tables (MySQL uses a very compact table format ).

28th,What MySQL should avoid

Use the deleted rows to update or insert tables, combined with the SELECT statement that takes a long time.
Use HAVING in columns that can be placed in the WHERE clause.
JOIN is performed without the use of the key code or the key code is not unique enough.
JOIN a column of different column types.
Use the HEAP table if the = parameter is not used to match the entire key code.
I forgot to use a WHERE clause in UPDATE or DELETE in the MySQL monitoring program. To do this, use the -- I-am-a-dummy option of the mysql client program.

19th,Various MySQL locks

Internal table lock
Lock tables (applicable to all table types)
Get lock ()/release lock ()
Page locking (for BDB tables)
Alter table also locks the TABLE on the bdb table.
Lock tables allows a table to have multiple readers and a writer.
Generally, the WHERE lock has a higher priority than the READ lock to prevent the writer from doing so. For unimportant writers, you can use the LOW_PRIORITY keyword to make the lock processor preferred reader.
UPDATE LOW_PRIORITY SET value = 10 WHERE id = 10;

Thirty, giveMore information about MySQLTo better solve the problem, note that you can always remove (add NOTES) the MySQL function to make the query Portable:

SELECT /*! SQL _BUFFER_RESULTS */...
SELECT SQL _BUFFER_RESULTS...
MySQL is forced to generate a temporary result set. After all temporary result sets are generated, all tables are locked. This is helpful when the table is locked or the result is sent to the client for a long time.
SELECT SQL _SMALL_RESULT... GROUP...
Tells the optimizer that the result set contains only a few rows.
SELECT SQL _BIG_RESULT... GROUP...
Tells the optimizer that the result set will contain many rows.
SELECT STRAIGHT_JOIN...
Force optimizer to join the table in the order that appears in the FROM clause.
SELECT... FROM table_name [use index (index_list) | ignore index (index_list)] table_name2
Force MySQL to use/ignore the listed indexes.