How MySQL optimizes distinct

In many cases, a temporary table is required to combine the distinct of an order by.

Note that because distinct may use GROUP BY, you must be aware of how the MySQL tutorial uses the columns in the order BY or HAVING clause of a part of the selected column. MySQL extends the purpose of group by, so you can use columns or operations that do not appear in the group BY statement in the select list. This means "any possible values for this group." You can get better performance by avoiding sorting and grouping unnecessary items. For example, in the following inquiries, you do not need to group Customer.name:

Mysql> Select Order.custid, Customer.name, max (payments)

-> from Order,customer

-> WHERE Order.custid = Customer.custid

-> GROUP BY Order.custid;

In standard SQL, you must add Customer.name to the GROUP BY clause. In MySQL, if you don't run in ANSI mode, the name is superfluous.

Do not use this feature if you omit columns from the group by section that are not unique in the set! You will get the results of the unpredictability.

In some cases, you can use min () and Max () to get a special column value, even if he is not unique. The values from the columns that contain the smallest values in the row sequence are given below:

substr (Min concat (rpad (sort,6, '), column), 7)

In the 3.6.4 section, "The row with the maximum number of groups for a field."

Note that if you are trying to follow standard SQL, you cannot use an expression in the group by or ORDER BY clause. You can bypass this restriction by using an alias for an expression:

Mysql> Select Id,floor (value/100) as Val

-> from Tbl_name

-> GROUP by ID, Val order by Val;

However, MySQL allows you to use an expression in the group BY and ORDER BY clause. For example:

Mysql> Select ID, Floor (value/100) from Tbl_name to Rand ();
, "group by" with suppressed fields.

In most cases, the DISTINCT clause can be treated as a special case of group by. For example, the following two queries are equivalent:

SELECT DISTINCT C1, C2, C3 from T1 where C1 > const; Select C1, C2, C3 from T1 where C1 > const GROUP by C1, C2, C3; Because of this equivalence, optimizations that apply to group by queries also apply to queries with DISTINCT clauses. In this way, for more detailed information about the optimization of the distinct query,

, "How MySQL optimizes group by."
The most common way to satisfy a GROUP BY clause is to scan the entire table and create a new temporary table in which all rows of each group should be contiguous, and then use the temporary table to locate the group and apply the cumulative function (if any). In some cases, MySQL can do better by indexing access without creating a temporary table.

The most important prerequisite for using an index for group by is that all the group by columns reference the properties of the same index, and the index saves its keywords sequentially (for example, this is a B-tree index, not a hash index). The use of index access instead of temporary tables also depends on which part of the index is used in the query, the conditions specified for that section, and the cumulative function that is selected.

There are two ways to execute a group by query through index access, as described in the following section. In the 1th method, the combination operation is used (if any) with all ranges of judgment. The 2nd method first performs a range scan and then combines the resulting tuples.

7.2.13.1. Loose Index Scan
The most efficient way to use an index is to search the group domain directly. With this access method, MySQL uses the properties of the indexed type (for example, B-tree) sorted by some keywords. This property allows lookup groups in the index to be used without having to consider all the keywords in the index that satisfy all where conditions. Since the access method only considers a small portion of the keywords in the index, it is called a loose index scan. If there is no WHERE clause, the number of keywords read by a loose index scan is as large as the number of groups, and can be much smaller than all the key words. If the WHERE clause contains a range-judging (for a discussion of the range join type, see section 7.2.1, "Explain syntax (get information about Select)"), loose index scans find the 1th keyword for each group that satisfies the range criteria, and read the fewest number of keywords again. It is possible under the following conditions:

· The query is for a single table.

· Group by includes the 1th contiguous portion of the index (if the query has a distinct clause for GROUP BY, all explicit properties point to the beginning of the index).

· Use only the cumulative function (if any) min () and Max (), and they all point to the same column.

· Any other part of the index (except those that are referenced from the query) must be constant (that is, they must be referenced by the number of constants), but the min () or Max () function has an exception to the arguments.

The explain output of such a query displays the using indexforgroup-by of the extra column.

The following query provides several examples of the class, assuming that the table T1 (C1,C2,C3,C4) has an index idx (C1,C2,C3):

Select C1, c2 from T1 GROUP by C1, C2;

SELECT DISTINCT c1, c2 from T1;

Select C1, min (C2) from T1 GROUP by C1;

Select C1, c2 from T1 where C1 < const GROUP by C1, C2;

Select Max (C3), Min (C3), C1, c2 from T1 where C2 > const GROUP by C1, C2;

Select C2 from T1 where C1 < const GROUP by C1, C2;

Select C1, c2 from T1 where c3 = Const GROUP by c1, C2;

For these reasons, you cannot use the Quick selection method to execute the following query:

1. In addition to Min () or Max (), there are other cumulative functions, such as:

Select C1, SUM (C2) from T1 GROUP by C1;2. The fields in the GROUP BY clause do not reference the beginning of the index, as follows:

Select C1,C2 from T1 GROUP by C2, C3;3. The query references a part of the keyword that follows the group by section and does not have an equation equal to the constant, for example:

Select c1,c3 from T1 GROUP by C1, c2;7.2.13.2. Compact Index Scan
A compact index scan can be scanned for an index scan or a range index, depending on the query criteria.

If the loose index scan condition is not met, the group by query can still not create temporary tables. If there is a scope condition in the WHERE clause, the method reads only the keywords that satisfy those conditions. Otherwise, an index scan is performed. This method reads all the keywords for each range defined by the WHERE clause, or does not scan the entire index with a range condition, which we define as a compact index scan. Note that for a compact index scan, all the keywords that meet the range criteria are found before the combination operation.

For this method to work, there is a constant equality condition that is sufficient for all columns in the query that references the preceding, intermediate keyword elements of the group BY keyword element. The constants in the equality condition populate the "gaps" in the search keyword and can form the full index prefix. These index prefixes can be used for index lookups. If you need to sort the group by results and can form the Search keyword for the index prefix, MySQL can also avoid extra sorting operations, because searching with the prefix of a sequential index retrieves all the keywords sequentially.

The first of these methods is not appropriate for the following query, but the 2nd method of indexing access works (assuming we have mentioned the index of table T1 IDX):

· There is a gap in group by, but it is already covered by the condition C2 = ' a '.

Select c1,c2,c3 from t1 where c2 = ' A ' GROUP by c1,c3; Group by does not start with the 1th element of the keyword, but there is a condition that provides constants for that element:

Select c1,c2,c3 from t1 where C1 = ' A ' group by C2,C3;

After combining limit Row_count and distinct, MySQL discovers the only Row_count line to stop immediately after.

If you do not use the columns of all the tables named in the query, MySQL stops scanning the unused table immediately after the 1th match is found. In the following scenario, assuming that T1 is used before T2 (which can be checked by explain), after discovering line 1th in T2, MySQL no longer reads T1 for any of the rows in T2:

SELECT distinct t1.a from T1, T2 where t1.a=t2.a;