How MySQL improves the statistical query speed of tables

This tutorial is going to tell you about the MySQL tutorial how to improve the table's statistical query speed Oh, we talked about using the middle table to improve the query and statistical speed of the datasheet.

For tables with a large database tutorial, it is usually slower to make a statistical query , and also to consider whether the query will affect the online application, usually in which case we use the intermediate table to increase the query statistics speed. Below we will count the counttable to count the customer daily expense record, as follows.

Create Tabel counttable (Id,varchar), Custi decimal (16,2), coutdate date,custip-varchar ()}

As a result of a large number of customer records every day, so the data volume of this table will be very large, now the business unit has a request, want to count the total money, we look at the example below.

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

SUM (custi)

161666666666

1row int Set (3.95 sec)

Method Two, use the intermediate table to handle the Tmp_cout

Create Tabel Tmp_count (Cid,varchar), Custcount (Decimal (16,2), custdate date,custip varchar (20)};

Now to transfer the data to be counted in the middle table for statistical and statistical results

Mysq>insert into the 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 amount of customer spending this week.

Mysql>select sum (cust_count) from Tem_count;

SUM (cust_count)

1696666666.22

1row in Set (0.73 sec)

Well, from the top two ways, it takes a lot of difference to spend time,

Using partial MySQL data statistics in the middle table will not adversely affect the application

The intermediate table can increase the index flexibly, or add temporary new fields, so as to improve the effective method of statistical query.