MySQL Two-table check page sorting efficiency optimization

There are two tables in the database

T1 storing message information

+-----------+------------------+------+-----+---------+----------------+|Field|Type| Null | Key | Default |Extra|+-----------+------------------+------+-----+---------+----------------+|Id| int(Ten) unsigned|NO|Pri| NULL    |Auto_increment||Code| int(Ten) unsigned|NO|MUL| NULL    |                ||Detail| varchar( $)|NO|     | NULL    |                ||Object| varchar( -)|NO|     | NULL    |                ||Create_at| datetime         |NO|     | NULL    |                |+-----------+------------------+------+-----+---------+----------------+

T2 Storing message types

+--------------+---------------------+------+-----+---------+----------------+|Field|Type| Null | Key | Default |Extra|+--------------+---------------------+------+-----+---------+----------------+|Code| int(Ten) unsigned|NO|Pri| NULL    |Auto_increment||  Level        | tinyint(3) unsigned|NO|MUL| NULL    |                ||Type| varchar( -)|NO|     | NULL    |                ||Content| varchar( -)|NO|     | NULL    |                ||Status| tinyint(3) unsigned|NO|     | NULL    |                |+--------------+---------------------+------+-----+---------+----------------+

Now you need to sort the two tables in a single page.

T1 number of records 2.3 million, T2 table 200

Three types of query statements are written as follows:

1) the slowestSelectT1.*, T2.*  fromT1, T2whereT1.code=T2.codeOrder  byT1.create_atdescLimit1065701, -; time consuming: -. 48 Seconds2) Use the LeftJoinSELECTT1.*, T2. Level  as  Level, t2.content asContent fromT1leftJoinT2on T1.code=T2.codeOrder  byCreate_atdescLimit1065701, -; time consuming:9. 71 Seconds3Check the scope of the T1 table first, and then check with the T2 table.SelectT1.*, T2. Level  as  Level, t2.content asContent fromT1 Left JoinT2 onT1.code=T2.codeJoin(Selecta.ID fromT1 AJoin(SELECTId fromT1Order  byCreate_atdescLimit1065721, -) bwherea.ID=b.ID) TwhereT.id=t1.id; time-consuming:1.41 seconds

Summarize:

In multi-table page sorting, the biggest bottlenecks are in the sort, so we should maximize the optimization sort. In view of this, we first sorted out the qualifying ID in the T1 table, and then used the result set to check the T1, and to check with T2 to get all the required information.

Do not know whether there is a better way to optimize, also please Daniel guidance:)

MySQL Two-table check page sorting efficiency optimization