How to optimize the slow query of large mysql tables?

Optimization method for slow queries on large mysql tables: 1. Reasonably build indexes, usually queries using indexes are faster than not using indexes; 2. Create horizontal partitions for key fields, such as time fields, if the query conditions are frequent Querying through the time range can improve a lot of performance; 3. Establish a coarse-grained data table; 4. Use cache.

(Recommended tutorial: mysql video tutorial)

If the data table in the mysql database is too large, the query will slow down, so this How to optimize it? The following article will introduce to you the optimization method of slow query when the mysql database table is too large. It has certain reference value. Friends in need can refer to it.

The mysql database table is too large and the query is slow to optimize

1. Reasonably build an index

Usually it is better to use an index than not to use an index for query Quick, you can check whether the index is used through explain. The specific explain usage method, such as

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

When the query includes group by and the group by field belongs to the index field, if the query result cannot be determined by the group by loose or compact index, the group by operation will create a temporary table and sort according to the file (Using temporary; Using filesort) To get the result, the performance is often lower, but it is not absolute. Even if the index is used, it is not necessarily faster than the query without it.

2. Create partitions

Create horizontal partitions for key fields, such as the time field. If the query conditions are often queried through the time range, it can improve a lot of performance.

3. Establish a coarse-grained data table

Create a corresponding table according to the query, and periodically compress and transfer the repeated records to a new table. The granularity becomes larger and the data records become less.

4. Use cache

Use cache to cache the “hard-won” data of a query for a period of time, thereby improving efficiency.

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

The above is how to optimize the slow query of mysql large tables? For more details, please pay attention to other related articles on 1024programmer.com!