[Solved] Would it be better to do a sepparate query instead of a left JOIN?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Would it be better to do a sepparate query instead of a left JOIN?

Database type:

Only a couple of rows is returned when not using LEFT since there isn't always data in the associated table - recipe_preparation. This is one of a couple of tables with the same "issue".

SELECT r.id, r.name,
    rp.explanation preparation,
    g.name
FROM recipes r
    LEFT JOIN recipes_preparation rp ON r.id = rp.fk_recipes_id AND rp.in_use = 1
    JOIN glassware g ON r.fk_glassware_id = g.id

The reason to why the preparation details is in a sepparate table is because of the ability to suggest/submit a better explanation. This is so the user can add their own/select a preffered one, and connect it to their account..
The AND rp.in_use is for the public one.

Anyway; I've read somewhere that using LEFT is more expensive, and that it should be avoided if possible. I could query recipes and only JOIN glasses. Then, in my application, I could just do a new query on each recipes to check if there's any data to retrieve.

What would be the better solution?
and as I mentioned before; this is just one of several other tables with this "issue"..

UPDATE:
Here is a EER diagram that I'm working with:

EER Diagram

I've added a couple of more tables after this screenshoot was taken, and the in_use column in recipes_preparation (not indexed)..
Maybe this could tell a little more...

UPDATE #2:
I looked into the EXPLAIN statment, and this is the result I got - which didn't tell me much at first. Result of using EXPLAIN statement

Looks like it starts with table g(glasses)? And it looks like it uses ALL as the join type - which is not good since that is a full table scan according to the MySQL Refrence..
Also; "MySQL found no index to use for executing the query more efficiently" more than? - the PK? - or non at all?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `glassware` ADD INDEX `glassware_idx_id` (`id`);
ALTER TABLE `recipes_preparation` ADD INDEX `recipes_preparatio_idx_in_use_fk_id` (`in_use`,`fk_recipes_id`);
The optimized query:
SELECT
        r.id,
        r.name,
        rp.explanation preparation,
        g.name 
    FROM
        recipes r 
    LEFT JOIN
        recipes_preparation rp 
            ON r.id = rp.fk_recipes_id 
            AND rp.in_use = 1 
    JOIN
        glassware g 
            ON r.fk_glassware_id = g.id

Related Articles



* original question posted on StackOverflow here.