MySQL Learning Essay--Understanding Merge by example, difference of temptable algorithm

Example 1

Two algorithms for using the view merge and temptable are counted separately

Table Tb_phone in Market_price greater than 4000, and then query the view to find out less than 6000 of the phone

A simple summary of the results obtained: query out market_price greater than 4000 and less than 6000 of the mobile phone

Table data:

1. Merge Merging algorithm

   The execution of the merge, each time it executes, merges the SQL statements of the view with the SQL statements of the external query view, and finally executes.

   The following is a simulation of the merge algorithm using the regular SELECT statement:

   The execution result simulation is the same as the result of the algorithm:

2. TempTable Temporal table algorithm

   Query that performs the view before performing other operations

   A temporary table pattern that, whenever queried, generates a temporary table of results for the SELECT statement used by the view, and then queries within the current temporary table

   The following is the simulation of the temporal table algorithm using the regular SELECT statement:

   The execution result simulation is the same as the result of the algorithm:

Comparing the results of the two algorithms is also the same, it seems that the next instance will be different

Example 2

Two algorithms for using the view merge and temptable are counted separately

Table Tb_phone, products of the highest price (Market_price) for similar (CID) handsets

Table data:

You can first consider the implementation of the basic SELECT statement (not to repeat here):

First sort and then group

The next two algorithms are used:

1. Merge Merging algorithm

   The last sentence of the Select simulation merge algorithm has a syntax error, order by cannot be in group by, then execution will error, but in the view will not error, but directly skip the error part of the order by directly executed

   SELECT * FROM Tb_phone GROUP BY CID

   Final result ( error ):

2. TempTable Temporal table algorithm

   The result of the execution is the same as the normal statement, and the simulated statement is equally correct

   Final result ( correct ):

 

MySQL Learning Essay--Understanding Merge by example, difference of temptable algorithm