Optimizing INNER JOIN query performance

Question

I'm using a database that requires optimized queries and I'm wondering which one of those queries are the optimized one, I used a timer but the result are too close. so I do not have to clue which one to use.

QUERY 1:

SELECT A.MIG_ID_ACTEUR, A.FL_FACTURE_FSL , B.VAL_NOM, 
       B.VAL_PRENOM, C.VAL_CDPOSTAL, C.VAL_NOM_COMMUNE, D.CCB_ID_ACTEUR
   FROM MIG_FACTURE A 
      INNER JOIN MIG_ACTEUR B
         ON A.MIG_ID_ACTEUR= B.MIG_ID_ACTEUR
      INNER JOIN MIG_ADRESSE C
         ON C.MIG_ID_ADRESSE = B.MIG_ID_ADRESSE
      INNER JOIN MIG_CORR_REF_ACTEUR D
         ON A.MIG_ID_ACTEUR= D.MIG_ID_ACTEUR;

QUERY 2:

SELECT A.MIG_ID_ACTEUR, A.FL_FACTURE_FSL , B.VAL_NOM, B.VAL_PRENOM, 
       C.VAL_CDPOSTAL, C.VAL_NOM_COMMUNE, D.CCB_ID_ACTEUR
  FROM MIG_FACTURE A , MIG_ACTEUR B,  MIG_ADRESSE C, MIG_CORR_REF_ACTEUR D
  WHERE A.MIG_ID_ACTEUR= B.MIG_ID_ACTEUR
       AND C.MIG_ID_ADRESSE = B.MIG_ID_ADRESSE
       AND A.MIG_ID_ACTEUR= D.MIG_ID_ACTEUR;

You've tagged this for both MySQL and Oracle. Which database are you actually using? — Justin Cave, May 07 '14 at 15:13
I'd imagine that since these two queries represent the same thing, essentially, they shouldn't have much of a difference in speed (as you saw). I would use the first query for portability, and because it looks cleaner to my eyes — Marshall Tigerus, May 07 '14 at 15:14
A timer is useful, but you should use other performance measures (IO, CPU) and check the execution plan. If you do this you will see that the execution plan is the same for each. I think the ANSI 92 join syntax in your first example is generally accepted as more legible, and easier to convert to OUTER JOINs if required, and less prone to accidental cross joins. [This article](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) is aimed at SQL Server, but is mostly relevant to all DBMS. — GarethD, May 07 '14 at 15:16
possible duplicate of [SQL JOIN: is there a difference between USING, ON or WHERE?](http://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where) — GarethD, May 07 '14 at 15:16
Whether you use WHERE or JOIN isn't going to make any difference. If you really want help optimizing the query, get an EXPLAIN and examine your indexes. — Andy Lester, May 07 '14 at 15:23
I'm sorry i left Mysql tag, I'm actualy using an Oracle 11g database — Taoufik RIFAI, May 07 '14 at 15:25

score 4 · Answer 1 · answered May 07 '14 at 15:12

If you are asking whether it is more efficient to use the SQL 99 join syntax (a inner join b) or whether it is more efficient to use the older join syntax of listing the join predicates in the WHERE clause, it shouldn't matter. I'd expect that the query plans for the two queries would be identical. If the query plans are identical, performance will be identical. If the plans are not identical, that would generally imply that you had encountered a bug in the database's query parsing engine.

Personally, I'd use the SQL 99 syntax (query 1) both because it is more portable when you want to do an outer join and because it generally makes the query more readable and decreases the probability that you'll accidentally leave out a join condition. That's solely a readability and maintainability consideration, though, not a performance consideration.

score 2 · Accepted Answer · answered May 07 '14 at 15:19

First things first:

"I used a timer but the result are too close" -- This is actually not a good way to test performance. Databases have caches. The results you get back won't be comparable with a stopwatch. You have system load to contend with, caching, and a million other things that make that particular comparison worthless. Instead of that, try using EXPLAIN to figure out the execution plan. Use SHOW PROFILES and SHOW STATUS to see where and how the queries are spending time. Check last_query_cost. But don't check your stopwatch. That won't tell you anything.

Second: this question can't be answered with the info your provided. In point of fact the queries are identical (verify that with Explain) and simply boil down to implicit vs explicit joins. Doesn't make either one of them optimized though. Again, you need to dig into the join itself and see if it's making use of indices, for example, or if it's doing a lot temp tables or file sorts.

Optimizing the query is a good thing... but these two are the same. A stop watch won't help you. Use explain, show profiles, show status.... not a stop watch :-)

Optimizing INNER JOIN query performance

2 Answers2