[Original] log table Design Example Analysis

In the final analysis, there are two aspects to the design of a relational table. First, it is designed completely according to the paradigm theory. Generally, it is enough to reach the third paradigm, or you can divide it to a further level. For example, the fourth and fifth

In the final analysis, there are two aspects to the design of a relational table. First, it is designed completely according to the paradigm theory. Generally, it is enough to reach the third paradigm, or you can divide it to a further level. For example, the fourth and fifth

In the final analysis, there are two aspects to the design of a relational table.

First, it is designed completely according to the paradigm theory. Generally, it is enough to reach the third paradigm, or you can divide it to a further level. For example, fourth, fifth, and sixth. This design has its own strong readability, but it adds the overhead of associating multiple Relational Tables When retrieving data.

Second, we should do some anti-paradigm in the paradigm theory. Some things should not be too stripped away. (Narrow tables and wide tables) This is consistent with the tightly coupled loose coupling theory in software design.

In the following example, we will use a common LOG table. There are two types of tables: narrow table and slightly wider table.

Narrow table: log_ytt

Mysql> show create table log_ytt; + ------------- + partition + | Table | Create Table | + ------------- + partition + | log_ytt | create table 'Log _ ytt' ('id' bigint (20) default null, 'Log _ time' datetime default null, key'idx _ u1' ('kids', 'Log _ Time ')) ENGINE = InnoDB default charset = utf8 | + ------------- + response --------------------------------------------- + 1 row in set (0.00 sec)

Number of table records

Mysql> select * from log_ytt where ids> '123 '; + ------------ + hour + | ids | log_time | + ------------ + --------------------- + | 7110000001 | 21:56:42 | 6300000001 | 21:56:42 | 21:56:42 | 7200000001 | 21:56:42 | 7380000001 | 21:56:42 | 5760000001 | 21:56:42 | 6930000001 | 21:56:42 | 6660000001 | 21:56:42 | 5670000001 | 21:56:42 | 21:56:42 | 6210000001 | | 5850000001 | 21:56:42 | 6570000001 | 21:56:42 | 5580000001 | 21:56:42 | 5130000001 | 21:56:42 | 21:56:42 | 7290000001 | 21:56:42 | 6390000001 | 5490000001 | 21:56:42 | 5220000001 | 21:56:42 | 7560000001 | 21:56:42 | 7470000001 | 21:56:42 | 7020000001 | 21:56:42 | 6840000001 | 21:56:42 | 6030000001 | 21:56:42 | 6480000001 | 21:56:42 | 7650000001 | 21:56:42 | 5940000001 | 21:56:42 | 6120000001 | 21:56:42 | 7740000001 | 21:56:42 | 5400000001 | 21:56:42 | 03:19:07 | 5760000001 | | 6840000001 | 03:19:17 | 7020000001 | 03:19:32 | 7200000001 | 03:19:45 | 7110000001 | 03:19:46 | 03:19:48 | 7380000001 | 03:19:58 | 5670000001 | 6930000001 | 03:19:59 | 6030000001 | 03:20:00 | 5940000001 | 03:20:00 | 7290000001 | 03:20:02 | 6120000001 | 03:20:09 | 5850000001 | 03:20:18 | 5580000001 | 03:20:24 | 6480000001 | 03:25:05 | 6390000001 | 03:25:37 | 6210000001 | 03:25:45 | 7470000001 | 03:26:14 | 6750000001 | 03:27:17 | 5310000001 | 03:27:33 | 03:27:34 | 5130000001 | | 6570000001 | 03:27:34 | 7560000001 | 03:27:45 | 5220000001 | 03:27:45 | 5400000001 | 03:27:53 | 03:27:55 | 5490000001 | 03:28:07 | 6660000001 | 6300000001 | 03:28:13 | 7740000001 | 03:28:26 | 7650000001 | 03:28:37 | + ------------ + --------------------------- + 60 rows in set (0.00 sec)

Next, we need to retrieve the average time of all IDS. There are two methods: