[Solved] difficulty with mysql join (nulls not showing)

EverSQL Database Performance Knowledge Base

difficulty with mysql join (nulls not showing)

Database type:

So I have 6 tables all with 2 columns. 3 have the data I'm using and the other 3 just specify how they are related.

So with this example data:

central_item

id   name
-----------------
1    Chicken
2    Shrimp


cooking_method

id   name
-----------------
1    Bake
2    Fry


style

id   name
-----------------
1    Casserole
2    Pie


central_item_cooking_method

central_item  cooking_method
------------------------------
1             1
1             2
2             1
2             2


central_item_style

central_item  style
------------------------------
1             1
1             2
2             1


cooking_method_style

cooking_method  style
------------------------------
1               1
2               2

I'm trying to get this:

central_item_name  style_name  cooking_method_name
----------------------------------------------------
Chicken            null        Bake
Chicken            null        Fry
Chicken            Casserole   null
Chicken            Casserole   Bake
Chicken            Pie         null
Chicken            Pie         Bake
Shrimp             null        Bake
Shrimp             null        Fry
Shrimp             Casserole   null
Shrimp             Casserole   Bake

This is the query I've been trying. The comments explain what each part is supposed to do. When I run it the query is missing a lot of results where I would expect to see just 1 column null.

SELECT
#name these something better so they don't all get returned as just 'name'
central_item.name as `central_item_name`, style.name as `style_name`, cooking_method.name as `cooking_method_name`
#we need a central item no matter what so start here
FROM central_item
#get styles for items (optional)
LEFT JOIN central_item_style ON central_item_style.style = central_item.id OR central_item_style.style IS NULL
#get names for any matching styles
LEFT JOIN style ON style.id = central_item_style.style OR style.id IS NULL
#get cooking methods for items (optional)
LEFT JOIN central_item_cooking_method ON central_item_cooking_method.central_item = central_item.id OR central_item_cooking_method.central_item IS NULL
#get names for cooking methods
LEFT JOIN cooking_method ON cooking_method.id = central_item_cooking_method.cooking_method OR cooking_method.id IS NULL
#for the matching item cooking methods check which styles also match the cooking method.  For item styles check for matching cooking methods.
LEFT JOIN cooking_method_style ON cooking_method_style.style = central_item_style.style OR cooking_method_style.cooking_method = central_item_cooking_method.cooking_method

WHERE
#make sure we have at least one of these
(style.id IS NOT NULL OR cooking_method.id IS NOT NULL) AND
#cooking method or style can be null but we have both they need to be compatible with each other
(cooking_method.id IS NULL OR central_item_style.style = cooking_method_style.style) AND
(style.id IS NULL OR central_item_cooking_method.cooking_method = cooking_method_style.cooking_method)

#Remove duplicates
GROUP BY central_item.name, style.name, cooking_method.name
ORDER BY central_item.name, style.name, cooking_method.name

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 `central_item_cooking_method` ADD INDEX `central_cooking_idx_cooking_method` (`cooking_method`);
ALTER TABLE `central_item_style` ADD INDEX `central_style_idx_style` (`style`);
ALTER TABLE `cooking_method` ADD INDEX `cooking_method_idx_id` (`id`);
ALTER TABLE `cooking_method_style` ADD INDEX `cooking_style_idx_style` (`style`);
ALTER TABLE `style` ADD INDEX `style_idx_id` (`id`);
The optimized query:
SELECT
        central_item.name AS `central_item_name`,
        style.name AS `style_name`,
        cooking_method.name AS `cooking_method_name` 
    FROM
        central_item 
    LEFT JOIN
        central_item_style 
            ON central_item_style.style = central_item.id 
            OR central_item_style.style IS NULL 
    LEFT JOIN
        style 
            ON style.id = central_item_style.style 
            OR style.id IS NULL 
    LEFT JOIN
        central_item_cooking_method 
            ON central_item_cooking_method.central_item = central_item.id 
            OR central_item_cooking_method.central_item IS NULL 
    LEFT JOIN
        cooking_method 
            ON cooking_method.id = central_item_cooking_method.cooking_method 
            OR cooking_method.id IS NULL 
    LEFT JOIN
        cooking_method_style 
            ON cooking_method_style.style = central_item_style.style 
            OR cooking_method_style.cooking_method = central_item_cooking_method.cooking_method 
    WHERE
        (
            style.id IS NOT NULL 
            OR cooking_method.id IS NOT NULL
        ) 
        AND (
            cooking_method.id IS NULL 
            OR central_item_style.style = cooking_method_style.style
        ) 
        AND (
            style.id IS NULL 
            OR central_item_cooking_method.cooking_method = cooking_method_style.cooking_method
        ) 
    GROUP BY
        central_item.name,
        style.name,
        cooking_method.name 
    ORDER BY
        central_item.name,
        style.name,
        cooking_method.name

Related Articles



* original question posted on StackOverflow here.