Mysql Query Optimization Index application of the detailed _mysql

Poor SQL query statements can have a serious impact on the operation of the entire application, not only consuming more database time, but also impacting other application components.

As with other disciplines, optimizing query performance largely depends on the developer's intuition. Fortunately, databases like MySQL have a few help tools on their own. This article briefly discusses the three kinds of tools: Using indexes, using explain to analyze queries, and adjusting the internal configuration of MySQL.

MySQL allows you to index database tables so that you can quickly find records without having to scan the entire table in the first place, thereby dramatically speeding up the query. Each table can have up to 16 indexes, and MySQL also supports multiple-column indexes and Full-text searches.

Adding an index to a table is very simple, just call a CREATE INDEX command and specify its domain for the index.
List A gives an example:

Copy Code code as follows:

Mysql> CREATE INDEX idx_username on users (username);

Here, the username domain of the users table is indexed to make sure that the SELECT query statement referencing this field in the WHERE or HAVING clause runs faster than if no index was added. The show index command lets you see that the index has been created (List B).

It's worth noting that the index is like a double-edged sword. Indexing each field in a table is often unnecessary and is likely to slow down because MySQL has to index the extra work each time it inserts or modifies data into the table. On the other hand, it is also not a good idea to avoid indexing each field in a table, because the query operation slows down when you increase the speed at which you insert records. This requires finding a balance, for example, when designing an indexing system, it is a wise choice to consider the main function of the table (data repair and editing).