How to optimize mysql large table query if it is slow?

Mysql large table query slow optimization method: 1. Create an index reasonably. Usually, the query uses the index faster than without the index; 2. Establish horizontal partitioning of key fields, such as time fields. If the query conditions are often Querying through time range can improve a lot of performance; 3. Create a coarse-grained data table; 4. Use cache.

(Recommended tutorial: mysql video tutorial)

How to solve the query problem when the data table in mysql database is too large slows down, so how to optimize this? The following article will introduce to you the optimization method for making mysql database table too large and the query slow. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

Mysql database table is too large and slow query optimization

1. Create an index reasonably

Usually queries using indexes are faster than not using indexes. Please explain through explain You can check whether the index is used. Explain the specific usage method, such as

http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html

When querying When group by is included and the group by field belongs to an index field, if the query result cannot be determined by group by loose or compact index, the group by operation will create a temporary table to get the results based on file sorting (Using temporary; Using filesort), often with poor performance It is lower, but it is not absolute. Even if the index is used, the query is not necessarily faster than the query without using it.

2. Create partitions

Establish horizontal partitions for key fields, such as time fields. If query conditions are often queried through time ranges, a lot of performance can be improved.

3. Create a coarse-grained data table

Create a corresponding table based on the query, and regularly compress and transfer duplicate records to a new table. The granularity becomes larger and the data records become smaller.

4. Use cache

Use cache to cache the "hard-earned" data of a query for a period of time, thereby improving efficiency.

For more programming related knowledge, please visit: Programming Video! !

The above is the detailed content of How to optimize mysql large table query if it is slow?. For more information, please follow other related articles on the PHP Chinese website!