How does mysql increase the statistical query speed of a table?

For large tables in the database, statistical queries are usually slow, and you need to consider whether the query will affect online applications, in this case, we can use an intermediate table to increase the query statistics speed. Next we will count counttable to count the daily consumption records of customers, as shown below.

This tutorial will tell you aboutHow to improve the statistical query speed of a table in the mysql tutorialOh, here we are talking about using an intermediate table to speed up data Table query and statistics.

For tables with large database tutorialsStatistics QueryThe query speed is usually slow, and it also needs to be considered whether the query will affect online applications. In this case, we can use an intermediate table to increase the query statistics speed, next we will count counttable to count the daily consumption records of customers, as shown below.

Create tabel counttable (id, varchar (10), custi decimal (16, 2), coutdate date, custip varchar (20 )}

Because a large number of customer records are generated every day, the amount of data in this table will be large. Now the business department has a requirement to count the total amount of money consumed. Let's look at the example below.

 

Mysq> select sum (custi) from counttable where custdate> adddate (now (),-7 );

Sum (custi)

161666666666

1row int set (3.95 sec)

Method 2: Use an intermediate table to process tmp_cout

Create tabel tmp_count (cid, varchar (10), custcount (decimal (16, 2), custdate date, custip varchar (20 )};

Now, transfer the data to the intermediate table for statistics and generate the results.

Mysq> insert into tmp_count select * from counttalbe wher custdate <adddate (now,-7 );

Query OK 15777777 rows affected (6.67 sec)

Records 15777777 duplicates: 0 warings 0;

Now let's count the customer's consumption amount this week.

Mysql> select sum (cust_count) from tem_count;

Sum (cust_count)

1696666666.22

1row in set (0.73 sec)

Well, from the two methods above, there are many differences in the time spent,

Use mysql of the intermediate tableData Statistics, Will not have a negative impact on the Application

An intermediate table can flexibly add indexes or add temporary new fields to improve the effective statistical query method.