How does JOIN work in MySQL?

Question

Although the question title is duplicate of many discussions, I did not find a answer to this question:

Consider a simple join for normalized tables of tags as

SELECT tags.tag
FROM tags
    INNER JOIN tag_map
    ON tags.tag_id=tag_map.tag_id
WHERE article_id=xx

Does JOIN work with the entire tables of tags and tag_map then filter the created (JOINed) table to find rows with WHERE clause for the article id

OR JOIN will only join rows of tag_map table in which article_id=xx ?

The latter method should be quite faster!

Answer 1

It will do the former, to my knowledge WHERE's are explicitly performed on the resulting JOINed table. (Disclaimer: MySQL may optimize this in some cases, I don't know).

To force the latter behaviour and execute the WHERE first, you can add an extra filter to your JOIN ON statement:

SELECT tags.tag 
    FROM tags 
    INNER JOIN tag_map 
        ON tags.article_id=xx
        AND tags.tag_id=tag_map.tag_id 
    WHERE article_id=xx

Answer 2

The Joins work on ONLY those records qualified from the WHERE clause of the first table returning records.. That said, you are doing a join to tag_map, but your where clause does not specify which alias the "Article_ID" is associated with. Its typically better to always qualify your fields with either the table name or alias the are coming from.

So, if article_id is coming from TAGS, then it will first look at that list as the primary set of records, and optimized with index if one so exists and return a small set. From that, the join is applied to the tag_map and will grab all records that match the join "ON" condition.

Just to clarify something. If the JOIN was applied FIRST, before the WHERE clause optimization, queries would take forever. The join basically PREPARES the relationship before the record selection actually occurs. Hence, the execution plan that shows the indexes that would be used.

Answer 3

You can always check Execution plan to see how your query gets executed step by step. In MySQL I don't know whether it can be presented graphically using any third party tools (as you can on MS SQL out of the box with Management Studio) but you can still check it using explain language constructs. Check documentation.

Not knowing your table schema

If article_id is of table tags then tag_map table isn't scanned at all unless join column in FK table is nullable.

If article_id is indexed (ie. primary key) then index is being scanned...

etc...

What I'd like to say is that we'd need your table schema definition to tell you some details. We can't know how your schema works.

Answer 4

Assuming it is simple or inner join:

The answer is: in relational model, first answer is correct, it creates a table that contains every row from first crossed with every row from the second table, so if you have N rows in first and M in second, it will create a table with NxM and then eliminate those where conditions do not match.

Now, that is mathematical model, but in implementation, depending on the engine, it will use some smarter way, typically choosing one table that seems faster and jioing from there using hopefully indexed join field. But this depends between engines: there is a lot of documetnation on that(google it) and some people, poster of this answer included, are paid to optimize join queries...

In case of MYSQL (just noticed the tag) you can use following syntax:

  EXPLAIN [EXTENDED] SELECT select_options

as explained here and MYSQL will tell you how it would execute such query. It is faster then reading the docuemtnation.

Answer 5

It depends on the engine. Earlier version of many database engines would generate the join results first and then it would filter. Newer versions of engines generate a execution plan that achieves the fastest results. Test would have to be done with the db engine reviewing execution plans for your version/database to find "what is best"