Search code examples
mysqlsql-optimization

Different SQL query efficiency

There are business requirements in the project that relate to a few tables. The following two query options are beneficial for performance optimization in those cases. How should I choose? First:Filter the Cartesian product:

select table1.a ,table2.b from table1 ，table2 where table1.id=table2.id

Second:Left outer connection mode or right outer connection mode 

 select table1.a ,table2.b from table1 left join table2 on table1.id=table2.id

Please tell me. Thank you very much.

Solution

  • If you write them in a form where they give the same results, expect the query analyser to give you identical* query plans. That said Use JOIN not ,, because , is much less clear, and has been deprecated for decades

    If you want to compare the performance of two different queries, the easiest method is to run both and compare how long they took, although checking that they are doing different things by comparing the query plans (EXPLAIN query text here in mysql will give you the plan for a query)

    Note that saying "I know they give different results, but I want the faster one" is never a sensible thing to say in programming. You should always know exactly what results you want.

    I.e.

    select table1.a ,table2.b from table1 ，table2 where table1.id=table2.id

    has the same meaning as

    select table1.a ,table2.b from table1 join table2 on table1.id=table2.id

    and 

    select table1.a ,table2.b from table1 left join table2 on table1.id=table2.id

    has the same meaning as 

    select table1.a ,table2.b from table1 ，table2 where table1.id=table2.id 
union 
select table1.a , null from table1 where table1.id not in (select table2.id from table2)

    And the join forms that you didn't use:

    select table1.a ,table2.b from table1 right join table2 on table1.id=table2.id

    has the same meaning as 

    select table1.a ,table2.b from table1 ，table2 where table1.id=table2.id 
union 
select null, table2.b from table2 where table2.id not in (select table1.id from table1)

    And

    select table1.a ,table2.b from table1 full join table2 on table1.id=table2.id

    has the same meaning as 

    select table1.a ,table2.b from table1 ，table2 where table1.id=table2.id 
union 
select table1.a , null from table1 where table1.id not in (select table2.id from table2)
union 
select null, table2.b from table2 where table2.id not in (select table1.id from table1)