Optimize a MySQL query with UNIONs

Question

I need to run a long MySQL query which contains several UNIONs. Some subqueries are taking much time resulting a long run time. I have made indexes on tables but still it takes around 15 seconds to run. I need to reduce the run time to 1 second.

Here is the query:

SELECT p.prd_id, NULL AS manu_name, NULL AS brand_name, NULL AS Categories, NULL AS Item_Color, NULL AS Ink_Type, NULL AS Industry,
 NULL AS Size, NULL AS Decoration_Method, NULL AS Rush_Production, NULL AS Themes, NULL AS Material, NULL AS Pattern, NULL AS Country_Origin, NULL AS Ships_From, pi.prd_img_name
 FROM products p
 LEFT JOIN product_image_p pi ON p.prd_id = pi.prd_id
 WHERE p.is_deleted = 'no' 

 UNION
SELECT p.prd_id, NULL AS manu_name, NULL AS brand_name, NULL AS Categories, NULL AS Item_Color, 
 NULL AS Ink_Type, NULL AS Industry, NULL AS Size, GROUP_CONCAT(decoration.dm_name SEPARATOR ', ') AS Decoration_Method, NULL AS Rush_Production, NULL AS Themes, NULL AS Material, 
 NULL AS Pattern, NULL AS Country_Origin, NULL AS Ships_From, NULL AS prd_img_name
 FROM products p
 JOIN product_dm pdm ON pdm.prd_id = p.prd_id
 JOIN decoration_method decoration ON decoration.dm_id = pdm.dm_id
 WHERE p.is_deleted = 'no'
 group by prd_id

 UNION

SELECT p.prd_id, NULL AS manu_name, NULL AS brand_name, NULL AS Categories, NULL AS Item_Color, 
 NULL AS Ink_Type, NULL AS Industry, NULL AS Size, NULL AS Decoration_Method, GROUP_CONCAT(rush.rush_title SEPARATOR ', ') AS Rush_Production, NULL AS Themes, NULL AS Material, 
 NULL AS Pattern, NULL AS Country_Origin, NULL AS Ships_From, NULL AS prd_img_name
 FROM products p
 JOIN product_rush_title prt ON prt.prd_id = p.prd_id
 JOIN rush_title rush ON rush.rush_id = prt.rush_id
 WHERE p.is_deleted = 'no'
 group by prd_id

 UNION
SELECT p.prd_id, NULL AS manu_name, NULL AS brand_name, NULL AS Categories, GROUP_CONCAT(ao.option_name SEPARATOR ', ') AS Item_Color, 
 NULL AS Ink_Type, NULL AS Industry, NULL AS Size, NULL AS Decoration_Method, NULL AS Rush_Production, NULL AS Themes, NULL AS Material, NULL AS Pattern, 
 NULL AS Country_Origin, NULL AS Ships_From, NULL AS prd_img_name
 FROM products p
 JOIN product_attributes pa ON pa.prd_id = p.prd_id 
 JOIN attributes_options ao ON ao.attr_opt_id = pa.attr_opt_id AND ao.attr_id = 19
 WHERE p.is_deleted = 'no' 
 group by prd_id

 UNION
 SELECT p.prd_id, NULL AS manu_name, NULL AS brand_name, NULL AS Categories, NULL AS Item_Color, 
 GROUP_CONCAT(ao.option_name SEPARATOR ', ') AS Ink_Type, NULL AS Industry, NULL AS Size, NULL AS Decoration_Method, NULL AS Rush_Production, NULL AS Themes, NULL AS Material, 
 NULL AS Pattern, NULL AS Country_Origin, NULL AS Ships_From, NULL AS prd_img_name
 FROM products p
 JOIN product_attributes pa ON pa.prd_id = p.prd_id 
 JOIN attributes_options ao ON ao.attr_opt_id = pa.attr_opt_id AND ao.attr_id = 10
 WHERE p.is_deleted = 'no'
 group by prd_id

 UNION
 SELECT p.prd_id, NULL AS manu_name, NULL AS brand_name, NULL AS Categories, NULL AS Item_Color, 
 NULL AS Ink_Type, GROUP_CONCAT(ao.option_name SEPARATOR ', ') AS Industry, NULL AS Size, NULL AS Decoration_Method, NULL AS Rush_Production, NULL AS Themes, NULL AS Material, 
 NULL AS Pattern, NULL AS Country_Origin, NULL AS Ships_From, NULL AS prd_img_name
 FROM products p
 JOIN product_attributes pa ON pa.prd_id = p.prd_id 
 JOIN attributes_options ao ON ao.attr_opt_id = pa.attr_opt_id AND ao.attr_id = 18
 WHERE p.is_deleted = 'no'
 group by prd_id

 UNION
 SELECT p.prd_id, NULL AS manu_name, NULL AS brand_name, NULL AS Categories, NULL AS Item_Color, 
 NULL AS Ink_Type, NULL AS Industry, GROUP_CONCAT(ao.option_name SEPARATOR ', ') AS Size, NULL AS Decoration_Method, NULL AS Rush_Production, NULL AS Themes, NULL AS Material, 
 NULL AS Pattern, NULL AS Country_Origin, NULL AS Ships_From, NULL AS prd_img_name
 FROM products p
 JOIN product_attributes pa ON pa.prd_id = p.prd_id 
 JOIN attributes_options ao ON ao.attr_opt_id = pa.attr_opt_id AND ao.attr_id IN (1, 13, 14)
 WHERE p.is_deleted = 'no'
 group by prd_id

 UNION
 SELECT p.prd_id, NULL AS manu_name, NULL AS brand_name, NULL AS Categories, NULL AS Item_Color, 
 NULL AS Ink_Type, NULL AS Industry, NULL AS Size, NULL AS Decoration_Method, NULL AS Rush_Production, GROUP_CONCAT(ao.option_name) AS Themes,
 NULL AS Material, NULL AS Pattern, NULL AS Country_Origin, NULL AS Ships_From, NULL AS prd_img_name
 FROM products p
 JOIN product_attributes pa ON pa.prd_id = p.prd_id 
 JOIN attributes_options ao ON ao.attr_opt_id = pa.attr_opt_id AND ao.attr_id = 17
 WHERE p.is_deleted = 'no'
 group by prd_id

 UNION
 SELECT p.prd_id, NULL AS manu_name, NULL AS brand_name, NULL AS Categories, NULL AS Item_Color, 
 NULL AS Ink_Type, NULL AS Industry, NULL AS Size, NULL AS Decoration_Method, NULL AS Rush_Production, NULL AS Themes, GROUP_CONCAT(ao.option_name) AS Material, 
 NULL AS Pattern, NULL AS Country_Origin, NULL AS Ships_From, NULL AS prd_img_name
 FROM products p
 JOIN product_attributes pa ON pa.prd_id = p.prd_id 
 JOIN attributes_options ao ON ao.attr_opt_id = pa.attr_opt_id AND ao.attr_id = 12
 WHERE p.is_deleted = 'no'
 group by prd_id

 UNION
 SELECT p.prd_id, NULL AS manu_name, NULL AS brand_name, NULL AS Categories, NULL AS Item_Color, 
 NULL AS Ink_Type, NULL AS Industry, NULL AS Size, NULL AS Decoration_Method, NULL AS Rush_Production, NULL AS Themes, NULL AS Material, 
 GROUP_CONCAT(ao.option_name) AS Pattern, NULL AS Country_Origin, NULL AS Ships_From, NULL AS prd_img_name
 FROM products p
 JOIN product_attributes pa ON pa.prd_id = p.prd_id 
 JOIN attributes_options ao ON ao.attr_opt_id = pa.attr_opt_id AND ao.attr_id = 2
 WHERE p.is_deleted = 'no'
 group by prd_id

 UNION
 SELECT p.prd_id, NULL AS manu_name, NULL AS brand_name, NULL AS Categories, NULL AS Item_Color, 
 NULL AS Ink_Type, NULL AS Industry, NULL AS Size, NULL AS Decoration_Method, NULL AS Rush_Production, NULL AS Themes, NULL AS Material, 
 NULL AS Pattern, GROUP_CONCAT(ao.option_name) AS Country_Origin, NULL AS Ships_From, NULL AS prd_img_name
 FROM products p
 JOIN product_attributes pa ON pa.prd_id = p.prd_id 
 JOIN attributes_options ao ON ao.attr_opt_id = pa.attr_opt_id AND ao.attr_id = 11
 WHERE p.is_deleted = 'no'
 group by prd_id

 UNION
 SELECT p.prd_id, NULL AS manu_name, NULL AS brand_name, NULL AS Categories, NULL AS Item_Color, 
 NULL AS Ink_Type, NULL AS Industry, NULL AS Size, NULL AS Decoration_Method, NULL AS Rush_Production, NULL AS Themes, NULL AS Material, 
 NULL AS Pattern, NULL AS Country_Origin, GROUP_CONCAT(ao.option_name) AS Ships_From, NULL AS prd_img_name
 FROM products p
 JOIN product_attributes pa ON pa.prd_id = p.prd_id 
 JOIN attributes_options ao ON ao.attr_opt_id = pa.attr_opt_id AND ao.attr_id = 8
 WHERE p.is_deleted = 'no'
 group by prd_id

The one with ao.attr_id = 19 is very slow, takes more than one second. — Mainuddin, Jan 18 '17 at 18:22
Update: I removed all the left joins (and made INNER join) except brands (which I really need left join) and the time reduced to 5 seconds. — Mainuddin, Jan 18 '17 at 18:23
@DRapp -- I'm impressed at how much energy you put into helping the unfortunate soul. — Rick James, Jan 18 '17 at 19:37

score 3 · Accepted Answer · edited May 23 '17 at 12:33

The issue you are encountering is the repeated joining to the product attributes and product options and doing full queries and getting one record returned for each component you are looking for. Also, with all the parts that are coming from the options table, you could pre-query once for each product, then return each component as one record for the PRD_ID in question.

To help optimize that component I would strongly suggest (if not already there) the following indexes...

Table                 Index
product_attributes    ( prd_id, attr_opt_id )
attributes_options    ( attr_opt_id, attr_id, option_name )
products              ( is_deleted, prd_id )
product_dm            ( prd_id, dm_id )
decoration_method     ( dm_id, dm_name )
product_rush_title    ( prd_id, rush_id )
rush_title            ( rush_id, rush_title )

The indexes will be "covering" indexes so the system does not have to go back to the raw data pages to prepare the results for most of it

Then, to get each of the parts as an internal pre-query do something like...

Since the decoration and rush content come from other tables, those too can be prequeried and summarized based on just the product ID and then left-joined

SELECT
      p2.prd_id,
      GROUP_CONCAT( case when ao.attr_id = 19
                        then ao.option_name else null end SEPARATOR ', ') 
          AS Item_Color,
      GROUP_CONCAT( case when ao.attr_id = 10
                        then ao.option_name else null end SEPARATOR ', ') 
          AS Ink_Type,
      GROUP_CONCAT( case when ao.attr_id = 18
                        then ao.option_name else null end SEPARATOR ', ') 
          AS Industry,
      GROUP_CONCAT( case when ao.attr_id = 17
                        then ao.option_name else null end SEPARATOR ', ') 
          AS Themes,
      GROUP_CONCAT( case when ao.attr_id = 12
                        then ao.option_name else null end SEPARATOR ', ') 
          AS Material,
      GROUP_CONCAT( case when ao.attr_id = 2
                        then ao.option_name else null end SEPARATOR ', ') 
          AS Pattern,
      GROUP_CONCAT( case when ao.attr_id = 11
                        then ao.option_name else null end SEPARATOR ', ') 
          AS Country_Origin,
      GROUP_CONCAT( case when ao.attr_id = 8
                        then ao.option_name else null end SEPARATOR ', ') 
          AS Ships_From,
      GROUP_CONCAT( case when ao.attr_id IN ( 1, 13, 14 )
                        then ao.option_name else null end SEPARATOR ', ') 
          AS Size
   from
      products p2
         JOIN product_attributes pa 
            ON p2.prd_id = pa.prd_id
            JOIN attributes_options ao 
               ON pa.attr_opt_id = ao.attr_opt_id                   
   WHERE 
      p2.is_deleted = 'no'
   group by
      p2.prd_id

Then you can roll that entire query as a subquery into your primary query but use the simplified alias reference and each pre-group concat field result

SELECT 
      p.prd_id, 
      NULL AS manu_name, 
      NULL AS brand_name, 
      NULL AS Categories, 
      preQuery.Item_Color, 
      preQuery.Ink_Type, 
      preQuery.Industry,
      preQuery.Themes, 
      preQuery.Material, 
      preQuery.Pattern, 
      preQuery.Country_Origin, 
      preQuery.Ships_From, 
      preQuery.Size, 
      tmpDeco.Decoration_Method, 
      tmpRush.Rush_Production, 
      pi.prd_img_name
   FROM 
      products p
         LEFT JOIN product_image_p pi 
            ON p.prd_id = pi.prd_id

         LEFT JOIN
         ( THE PRE-QUERY SAMPLE ABOVE ) PreQuery
            ON p.prd_id = PreQuery.prd_id

         LEFT JOIN
         ( select
                 p2.prd_id,
                 GROUP_CONCAT(decoration.dm_name SEPARATOR ', ') AS Decoration_Method 
              FROM 
                 products p2
                    JOIN product_dm pdm 
                       ON p2.prd_id = pdm.prd_id
                       JOIN decoration_method decoration ;
                          ON decoration.dm_id = pdm.dm_id
              WHERE 
                 p2.is_deleted = 'no'
              group by 
                 p2.prd_id ) as tmpDeco
            ON p.prd_id = tmpDeco.prd_id

         LEFT JOIN
         ( select
                 p2.prd_id,
                 GROUP_CONCAT(rush.rush_title SEPARATOR ', ') AS Rush_Production
              FROM 
                 products p2
                    JOIN product_rush_title prt 
                       ON p2.prd_id = prt.prd_id
                       JOIN rush_title rush 
                          ON prt.rush_id = rush.rush_id
              WHERE 
                 p2.is_deleted = 'no'
              group by 
                 p2.prd_id ) as tmpRush
            ON p.prd_id = tmpRush.prd_id

    WHERE
        p.is_deleted = 'no'

Hopefully you are able to see the simplified context of getting all common description elements originating from the same table at once, then joining when finished based on the single alias references.

I might have a type-o or two but think it is correct otherwise in context to your needs. You also did not have any stuff for the Manu_Name, Brand_Name, Category so you will have to finish that component.

FOLLOW-UP...

Based on your latest, what I would do is add some fields to your product table to hold each of the fields. Then, create a trigger for insert/update/delete to the product attributes table when added for a product. When an impact, run just the simple sql-select group_concat() on the field in question and immediately update the main product table. Yes, this denormalizes to one extent, but completely simplifies your need to do the intensive queries and kill performance.

Take a look at my answer in this other stack question. I am creating such a similar trigger. This way, your primary table is already pre-aggregated and ready to go and the triggers should operate almost instantaneously since you would only be doing a single field for a single product.

thank your for this great effort; but your suggested query did not make any remarkable improvement. My query takes 5.7 seconds while yours take 4.7 seconds. I think I there is something else causing the bottleneck. — Mainuddin, Jan 20 '17 at 03:34
@Mainuddin, revised answer for clarification and redirect to another Stack post implementing TRIGGERs. — DRapp, Jan 20 '17 at 04:53
I finally decided to create a dedicated table for search which will contain only search related data. Triggers will keep it updated whenever a change made in products and other tables. That makes it super fast as expected. Thanks again @DRapp. — Mainuddin, Jan 21 '17 at 18:15

score 0 · Answer 2 · answered Jan 18 '17 at 19:19

Part of the performance issue may be due to "over-normalization". Notice how GROUP_CONCAT(ao.option_name) is not coming from the attribute table but via another JOIN. Suggest you get rid of ao and move the option_names into product_attributes.

(This is addition to @DRapp's answer, which seems, on the surface, to cover a number of ills in the query.)

Optimize a MySQL query with UNIONs

2 Answers2