Differences between SQL from multi-table and inner join

This is the inner join notation.

SELECT    B.dict_data_name,    SUM(A.pv) AS pvFROM    shw_mo_health_news AINNER JOIN bas_dict_data B ON A.third_name_dictid = B.item_idWHERE    A.class_level = 3AND B.class_id = 1012AND A.collect_date >= '2016-04-01'AND A.collect_date <= '2016-05-31'GROUP BY    A.third_name_dictidORDER BY    pv DESC;

Actual query time user 0.6S or so

View with explain:

This is the from multi-table notation

SELECT    B.dict_data_name,    A.PVFROM    (        SELECT            hn.third_name_dictid,            SUM(hn.pv) AS PV        FROM            shw_mo_health_news hn        WHERE            hn.class_level = 3        AND hn.collect_date >= '2016-04-01'        AND hn.collect_date <= '2016-05-31'        GROUP BY            hn.third_name_dictid    ) A,    (        SELECT            dd.item_id,            dd.dict_data_name        FROM            bas_dict_data dd        WHERE            dd.class_id = 1012    ) BWHERE    A.third_name_dictid = B.item_idORDER BY    PV DESC

Actual Time around 0.03s

Explain view

Why is there such a big difference in the efficiency of these two query methods?
Many on the internet say that the efficiency of the two writing is similar, but I this one is 0.6 and a 0.03, the difference is quite big, what is the cause of this? Is it because I wrote an SQL statement for a problem or another reason?

Reply content:

This is the inner join notation.

SELECT    B.dict_data_name,    SUM(A.pv) AS pvFROM    shw_mo_health_news AINNER JOIN bas_dict_data B ON A.third_name_dictid = B.item_idWHERE    A.class_level = 3AND B.class_id = 1012AND A.collect_date >= '2016-04-01'AND A.collect_date <= '2016-05-31'GROUP BY    A.third_name_dictidORDER BY    pv DESC;

Actual query time user 0.6S or so

View with explain:

This is the from multi-table notation

SELECT    B.dict_data_name,    A.PVFROM    (        SELECT            hn.third_name_dictid,            SUM(hn.pv) AS PV        FROM            shw_mo_health_news hn        WHERE            hn.class_level = 3        AND hn.collect_date >= '2016-04-01'        AND hn.collect_date <= '2016-05-31'        GROUP BY            hn.third_name_dictid    ) A,    (        SELECT            dd.item_id,            dd.dict_data_name        FROM            bas_dict_data dd        WHERE            dd.class_id = 1012    ) BWHERE    A.third_name_dictid = B.item_idORDER BY    PV DESC

Actual Time around 0.03s

Explain view

Why is there such a big difference in the efficiency of these two query methods?
Many on the internet say that the efficiency of the two writing is similar, but I this one is 0.6 and a 0.03, the difference is quite big, what is the cause of this? Is it because I wrote an SQL statement for a problem or another reason?

It should be the connection loss, I think because you use the conditions are not indexed caused. If the field is properly indexed, because MySQL automatically optimizes the SQL statement, the final query statement is the same, the performance is the same, and no index when the so-called optimization does not exist, this time the final query statement is basically equivalent to the SQL you commit.
You can try.

SELECT    B.dict_data_name,    SUM(A.pv) AS pvFROM    shw_mo_health_news AINNER JOIN bas_dict_data B ON A.class_level = 3 and A.collect_date >= '2016-04-01'AND A.collect_date <= '2016-05-31' and B.class_id=1012 and  A.third_name_dictid = B.item_idGROUP BY    A.third_name_dictidORDER BY    pv DESC;

I think the performance will be significantly different.

The same, belong to SQL-89 and SQL-92 different norms. See Https://en.wikipedia.org/wiki ...

Find a related question and answer, and one of the answers is exactly the https://community.microstrate of your question ...

Here the 2nd SQL has additional overhead (temporary table) because of the subquery.
Why is the 2nd SQL more than the 1th SQL, according to the execution plan does not see what, the feeling is just an exception does not explain what

In theory, subqueries and JOIN no essential differences should be equivalent after a reasonable optimization of the Query Analyzer. But it is also because of the various flaws of the Query Analyzer, some times some versions of the database subquery support better, some of the JOIN support is better. Most of the books I've seen in MySQL JOIN are equivalent, but be careful about the scenarios in which subqueries are located WHERE , such as:

-- 查询1SELECT * FROM table_aWHERE A IN (    SELECT A FROM table_b    WHERE B = 'x')-- 查询2SELECT table_a.* FROM table_a A    INNER JOIN table_b B ON a.A = b.AWHERE B.B = 'x'

This is the scenario where a typical MySQL query parser fails. table_aThe A field has an index in the case of the theoretical query 1 and query 2 should be equivalent, but in fact, MySQL 5.x version of the query 2 performance is significantly better than the query 1. This was discussed on StackOverflow, a known issue that existed for nearly 10 years. It must be upgraded to 6.0.x to be repaired. So, the surest way to a better problem is to explain jump to conclusions about the results you see.
For your two queries, in fact, the execution plan is not the same, it is obvious that the second consumes more, the time is almost just because the extra two steps to get the amount of data is not too large. Furthermore, these two queries are not equivalent in fact, they are not comparable.

