How MySQL implements cross-pants query

Scenario one, field redundancy. In other words, a part of the information is repeated, such as Pay_log table in addition to the UserID field and User_name field, and so on. However, this is only suitable for a small number of fields, if the registration, login time, level and so on all fields are repeated, it is obviously unscientific.

Scenario Two, table replication and synchronization. In other words, the User_info table inside the main_db is copied to the log_db, and then the scheduled task is set to synchronize the two tables. This can be done in a library, and then can be a JOIN and other complex statement operations. But the contents of the two tables are repetitive, which is a bit of a waste of space.

Program three, link table. What is a linked table? Simply put, there is a user_info table in the log_db, but the table does not store data, but is linked directly to the User_info table in main_db. In this way, we can do this without having to synchronize regularly, or we can use joins in the same library.

Using the code of the Federated engine, Federated is turned off by default, and needs to modify the configuration to turn on the engine

CREATE TABLE ' user_info ' (  ' userid ' int (one) ' Not NULL auto_increment COMMENT ' user ID ',  ' username ' varchar ' not NUL L default ' COMMENT ' account ',  ' level ' int (one) NOT null default ' 1 ' COMMENT ' rank ',  ' log_dt ' timestamp NOT NULL default ' 2016-11-30 00:00:00 ' COMMENT ' last login time ',  ' Reg_dt ' timestamp not NULL DEFAULT current_timestamp COMMENT ' registration time ',  PRIMARY key (' userid '),  key ' idx_name ' (' username ')) engine=federated DEFAULT Charset=utf8 row_format=compact connection= ' Mysql://batsing:[email protected]:3306/main_db/user_info '  comment= ' user basic information table-Link table ';

　　

Original: http://www.cnblogs.com/batsing/p/federated.html

How MySQL implements cross-pants query