Distinct select multiple fields, only distinct a single field solution

This article is based on the original author to add their own understanding summed up, if there are any questions to welcome you to discuss the exchange. First paste the original text as follows:

A foreground SQL statement, simplified after the following
SELECT products_name,products_viewed from ' products_description '
ORDER by products_viewed desc,products_name LIMIT 0, 20;

This statement often appears in the slow log in large batches.

Preliminary look at the change statement, very simple, according to products_viewed (product is viewed) in reverse order, and then sorted according to Products_name (product name). Indexes are established on products_viewed and Products_name respectively.
But it feels products_name sort of weird.
After explain found
+----+-------------+----------------------+------+---------------+------+---------+------+-------+------------- ---+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+----------------------+------+---------------+------+---------+------+-------+------------- ---+
| 1 | Simple | products_description | All | NULL | NULL | NULL | NULL | 764370 | Using Filesort |
+----+-------------+----------------------+------+---------------+------+---------+------+-------+------------- ---+

Change the statement to do a full table scan.

The order BY statement of MySQL, if there is no appropriate index selection in the Where condition, will select the index in the order by Col as the condition, but if it is a combination of multiple order by, it will cause the use of the index to be discarded.
and development as well as the need to communicate, found by name sorting is not needed.
We get rid of the products_name! by the back of the order
Once again explain found that the index was already available:
Explain SELECT products_name,products_viewed from ' products_description '
ORDER by products_viewed LIMIT 0, 20;
+----+-------------+----------------------+-------+---------------+-----------------+---------+------+------+-- -----+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+----------------------+-------+---------------+-----------------+---------+------+------+-- -----+
| 1 | Simple | products_description | Index | NULL | products_viewed | 5 |  NULL |      20 | |
+----+-------------+----------------------+-------+---------------+-----------------+---------+------+------+-- -----+

Again two times profiling (process omitted), found the first time to damage a large number of IO and CPU time sorting result. Because the statement for the foreground statement, there are a large number of queries, optimization, page opening speed significantly improved.

Attention:
1. Order by M,n do not easily write this statement, the general order by the front of the M is the priority by, the following n for supporting, if not necessary, as far as possible to remove
2. Refer to my other article http://www.linuxidc.com/Linux/2014-03/98549.htm

The above is written by the original author, the original link here: http://www.linuxidc.com/Linux/2014-03/98550.htm

Then I found out that the field following the order by was not queried by the previous SELECT statement, and some unpredictable results would occur. Now the results of my two queries are posted below, for the time being I do not know why this is happening.

For the first query, select the field after the order by, and the result screenshot is as follows:

The second query, the field following the order by is not followed by the Select, and the result screenshot is as follows:

The results of two queries are completely different, and using the distinct statement does not appear as expected: the only first column of data A and column data B associated with this column. Instead of distinct A, B, two columns of data. A review of the relevant forums finally found that you can use the Select A,b,count (*) from TableName GROUP by A has b>=1 to achieve the requirements. But the problem again, so the speed of the query is slow, because of the classification count, and so on, I use the following SQL statement query two 20w-level table, the time required to perform a period of 6s or so. The related columns that are queried are indexed.

Later thought that the database should only perform search, access to data functions, the rest to the background to perform, and then set up a new table, pseudo foreign key form to solve the problem. SQL is as follows:

CREATE TABLE Relation  as (select ' Litigationinfo '. ' Org-name ' as A, Max (' Litigationinfo '. ' Release-time ') as B,count ( DISTINCT ' litigationinfo '. ' Org-name ') as C from ' litigationinfo ', ' enterpriseinfo ' where  ' litigationinfo '. ' Org-name ' = ' enterpriseinfo '. ' Org-name ' GROUP by A has c=1 order by B DESC);

To further increase the query speed, you can establish an index:

ALTER  TABLE  ' relation '  ADD  PRIMARY  KEY (  ' A '  );
ALTER  TABLE  ' relation '  ADD  INDEX relation_index_2 (  ' B '  )