Difference between SQLfrom multi-table and innerjoin

This is the innerjoin statement {code ...} the actual query time is about ain and you can view it with explain: this is the multi-table from statement {code ...} in actual use, Explain about S. Why are the efficiency of the two query methods significantly different? Many people on the Internet say these two methods...This is the inner join statement.

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;

The actual query time is about S.

Use explain to view:

This is a multi-table from statement.

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

The actual time is around seconds.

View Explain

Why are the efficiency differences between the two query methods?
Many people on the Internet say that the efficiency of these two writing methods is almost the same. However, if I use 0.6 and 0.03, the difference between the two is quite large. why? Is there a problem with the SQL statement I wrote?

Reply content:This is the inner join statement.

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;

The actual query time is about S.

Use explain to view:

This is a multi-table from statement.

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

The actual time is around seconds.

View Explain

Why are the efficiency differences between the two query methods?
Many people on the Internet say that the efficiency of these two writing methods is almost the same. However, if I use 0.6 and 0.03, the difference between the two is quite large. why? Is there a problem with the SQL statement I wrote?

It should be connection loss. I think it is because you have no indexes. If the field is properly indexed, mysql automatically optimizes the SQL statement, and the final query statement is the same, with the same performance, when there is no index, the so-called optimization does not exist. at this time, the final query statement is basically equivalent to the SQL statement you submitted.
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.

Same, belonging to different specifications of SQL-89 and SQL-92. See https://en.wikipedia.org/wiki...

Find a related Q & A, which has a https://community.microstrate to answer your question...

The 2nd SQL statements have additional overhead (temporary table) due to subqueries ).
Why are 2nd SQL statements worse than 1st SQL statements? according to the execution plan, I didn't see much. I feel that it is just an exception and cannot explain anything.

Theoretically, subqueries andJOINThere is no essential difference. the query analyzer should be equivalent after reasonable optimization. However, due to the various defects of the query analyzer, sometimes some versions of the database provide better support for subqueries.JOINBetter support. For MySQL, most versions of subqueries andJOINIs equivalent, but be careful that the subquery is located inWHEREFor example:

-- Query 1 SELECT * FROM table_aWHERE a in (select a from table_ B where B = 'x') -- Query 2 SELECT table_a. * FROM table_a a inner join table_ B B ON. A = B. awhere B. B = 'X'

This is a typical MySQL Query analyzer failure scenario.table_aOfAWhen the field has an index, the theoretical query 1 and query 2 should be equivalent, but in fact, MySQL 5.x has a better performance than Query 1 in query 2. This is a known issue in Stackoverflow that has existed for nearly 10 years. You must upgrade to 6.0.x to fix the issue. Therefore, the most reliable way to solve a better problem is to look at it by yourself.explainThen draw conclusions.
For your two queries, the execution plan is actually different. it is obvious that the second query consumes more time, just because the data size obtained by the two extra steps is not too large. Furthermore, these two queries are not equivalent and cannot be compared.