MySQL: JOIN vs WHERE performance

Question

Question: What should I take into account when make performance comparison of those two queries?

Queries:

SELECT id 
FROM table 
WHERE id = (
    SELECT table_id_fk 
    FROM another_table 
    WHERE id = @id
)

vs

SELECT id 
FROM table t 
JOIN another_table at
ON t.id = at.table_id_fk
WHERE at.id = @id

Actually they do the same but in other ways.

P.S. It's not enough to just launch it on my server and look at response time. I want to understand the difference and understand what happens when, for instance, my db will grow up.

Answer 1

Performance is not always the main option when writing SQL queries. I would choose the first query for readability. "Give me the record of table that is referenced by the record of another_table with @id". This is straight-forward and easy to read. As to performance: You access one record by primary key to access another record (in another table) by Primary key. This can hardly get any faster.

The second statement joins both tables to get to the id (the selected id lacks the qualifier t by the way). So it does the same, but is not obvious at first glance. "Join both tables, but limit this to the another_table record with @id and give me table's id". This means the same, but gives the dbms the freedom to choose how to execute it. It could for instance join all records first and then remove all where @id doesn't match. However a good dbms won't do this; it will create the same execution plan as for statement 1.

Good dbms detect situations like these, they re-write qwueries internally, find out that the queries mean the same and come to the same execution plan. This is getting better and better, but it doesn't always work perfectly. So sometimes it does matter how to write the statement. When statements become more complex, then sometimes the second syntax to join everything and filter what you need leads to better execution plans. Unfortunately. So you often have to decide between readability and performance. I usually write my queries as readable as possible and change them only when it comes to performance problems.

Answer 2

According to me, you just want to ask which is better. A Co-related sub-query or a SQL Join. Here is the explanation:-

A "correlated subquery" (i.e., one in which the where condition depends on values obtained from the rows of the containing query) will execute once for each row. A non-correlated subquery or sql join (one in which the where condition is independent of the containing query) will execute once at the beginning. The SQL engine makes this distinction automatically.

So according to me join will give fastre result than the co-related subquery. But on real ground you have to check the performance of these queries on your system as well for actual result.

Answer 3

With a minor bit of investigation.

I have sympathy with the point above that readability is important, although I find the join to be readable while sub queries I find less readable (although in this case the sub query is quite simple so not a major issue either way).

Normally I would hope that MySQL would manage to optimise a non correlated sub query away and execute it just as efficiently as if it were a join. This sub query at first glance does appear to be non correlated (ie, the results of it do not depend on the containing query).

However playing on SQL fiddle this doesn't appear to be the case:-

http://www.sqlfiddle.com/#!2/7696c/2

Using the sub query the explain says it is an UNCACHEABLE SUBQUERY which from the manual is :-

A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query

Doing much the same sub query by specifying the value rather than passing it in as a variable gives a different explain and just describes it as a SUBQUERY . This I suspect is just as efficient as the join.

My feeling is that MySQL is confused by the use of the variable, and has planned the query on the assumption that the value of the variable can change between rows. Hence it needs to re execute the sub query for every row. It hasn't managed to recognise that there is nothing in the query that modifies the value of the variable.

If you want to try yourself here are the details to set up the test:-

CREATE TABLE `table`
(
    id INT,
  PRIMARY KEY id(id)
);

CREATE TABLE another_table
(
    id INT,
    table_id_fk INT,
    PRIMARY KEY id (id),
    INDEX table_id_fk (table_id_fk)
);

INSERT INTO `table`
VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8);

INSERT INTO another_table
VALUES
(11,1),
(12,3),
(13,5),
(14,7),
(15,9),
(16,11),
(17,13),
(18,15);

SQL to execute:-

SET @id:=13;

SELECT t.id 
FROM `table` t
WHERE id = (
    SELECT table_id_fk 
    FROM another_table 
    WHERE id = @id
);



SELECT t.id 
FROM `table` t 
JOIN another_table at
ON t.id = at.table_id_fk
WHERE at.id = @id;


SELECT t.id 
FROM `table` t
WHERE id = (
    SELECT table_id_fk 
    FROM another_table 
    WHERE id = 13
);

EXPLAIN SELECT t.id 
FROM `table` t
WHERE id = (
    SELECT table_id_fk 
    FROM another_table 
    WHERE id = @id
);



EXPLAIN SELECT t.id 
FROM `table` t 
JOIN another_table at
ON t.id = at.table_id_fk
WHERE at.id = @id;


EXPLAIN SELECT t.id 
FROM `table` t
WHERE id = (
    SELECT table_id_fk 
    FROM another_table 
    WHERE id = 13
);

Explain results:-

ID  SELECT_TYPE     TABLE   TYPE    POSSIBLE_KEYS   KEY     KEY_LEN     REF     ROWS    EXTRA
1   PRIMARY     t   index   (null)  PRIMARY     4   (null)  8   Using where; Using index
2   UNCACHEABLE SUBQUERY    another_table   const   PRIMARY     PRIMARY     4   const   1   

ID  SELECT_TYPE     TABLE   TYPE    POSSIBLE_KEYS   KEY     KEY_LEN     REF     ROWS    EXTRA
1   SIMPLE  at  const   PRIMARY,table_id_fk     PRIMARY     4   const   1   
1   SIMPLE  t   const   PRIMARY     PRIMARY     4   const   1   Using index

ID  SELECT_TYPE     TABLE   TYPE    POSSIBLE_KEYS   KEY     KEY_LEN     REF     ROWS    EXTRA
1   PRIMARY     t   const   PRIMARY     PRIMARY     4   const   1   Using index
2   SUBQUERY    another_table   const   PRIMARY     PRIMARY     4       1