1. What is associated query?

A) In the Select statement in MySQL, we often need to use join substatement for complex business. Left join, right join, inner Join, left Outer join, right Outer join, full join Let's test this: I. First of all, let's do some data, class table 200W data, user table 40W data ii. We then query all students under 15 classesCopy the code

Iv. We use 'explain' to test the performance of the SQL statementCopy the code

[Select_type] [Select_type] [Select_type] [Select_type] [Select_type] [Select_type] [Select_type] [Select_type] [Select_type] [Select_type] [Select_type] Important entries, showing the type used for the connection, sort by best to worst type.System table has only one row ii.const for iii.eq_ref the best possible connection type other than const when comparing PRIMARY KEY with constant values. It is used when all parts of an index are joined and the index is a UNIQUE or PRIMARY KEY. For each KEY, only one record in the table matches it. Iv.ref: The join cannot select a single row based on the keyword. It's called ref because the index is compared to some reference value. The reference value is either a constant or the result of a multi-table query from a single table v.ref_or_NULL: like ref, but MySQL must find the null entry in the first search result, and then perform the second search vi.index_merge: Unique_subquery: Use this type IN some IN queries instead of the regular ref: viii.ndex_subquery: This type is used IN some IN queries, similar to unique_subquery, but the query is non-unique index ix.Range: only rows IN a given Range are retrieved, using an index to select rows. The key column shows which index was used. When using the =, <>, >=, <, <=, IS NULL, <=>, BETWEEN, or IN operators to compare key columns with constants, you can use range xindex: full table scan, where tables are scanned IN index order instead of row order. All c)possible_keys D)Key E)Rows: How many Rows have been scanned in the current query I. Ii. For associated query, the total number of query rows in the current SQL is equal to the product of query rows in each SQL f)Extra: I. sing filesort indicates that MySQL will use an external index to sort the results, rather than reading them in index order from the table. Sorting may be done in memory or on disk. The sort operations that cannot be done with indexes in MySQL are called "file sort". Ii.Using Temporary Indicates that MySQL uses a temporary table when sorting query results. This is common for sorting order by and group BY. A) Add an index to CID in the user tableCopy the code

B) Even so, mysql has scanned the database for 410140 rows of 10*41014 3. In the company, join substatement is generally not allowed, because it consumes too much, so we will generally adopt a) the method of redundancy of data fields to reduce associated query, but increase the amount of data b) the method of using mysql logic to carry out associated query of data, which is also what we will talk aboutCopy the code

How to use PHP instead of associated query

A) Implementation method: i. array_column: extract a specified column of data from an array. B) Implementation method: i. array_column: extract a specified column of data from an arrayCopy the code

Ii. Now there are two sets of dataCopy the code

1. Merge the two sets of data based on the CID to display the class to which the user belongsCopy the code

2. In this way, there are too many nested loops and PHP efficiency is not high. If multiple tables are checked, there will be more loops iii. 2. Step: a) The relationship between the user and the class is multiple/one b) The association ID (CID) in the class (1) data is changed to the index of the arrayCopy the code

C) Then through the user (multiple) loop traversal, cid in the data goes to the class array to query the corresponding class nameCopy the code

Mysql of actual combat