2
\$\begingroup\$

I have a slow query on a wordpress site. How can I rewrite this to make it faster? I'm not having much luck. The query takes about 0.900 seconds I have both the query and the explain below, my goal is to modify this query to make it execute faster, thanks.

This is the query:

SELECT
    wrapper_featured_colorways.id,
    wrapper_featured_colorways.design_name AS dname,
    wrapper_featured_colorways.designId,
    wrapper_featured_colorways.submodel_id,
    wrapper_featured_colorways.sled_image,
    wrapper_featured_colorways.sled_png,
    wrapper_featured_colorways.colorway_name,
    wrapper_featured_colorways.engineId,
    wrapper_featured_colorways.yearText,
    wrapper_submodels.model_id,
    wrapper_submodels.sub_name,
    wrapper_models.model_name,
    wrapper_chassis.id AS chassis_id,
    wrapper_chassis.chassis_name,
    wrapper_manufacturers.mf_name,
    wrapper_manufacturers.mf_id,
    a.guid AS svgPath,
    afx_posts.post_type,
    afx_posts.post_title,
    GROUP_CONCAT(
        wrapper_featured_colorway_colors.color_name
    ORDER BY
        wrapper_featured_colorway_colors.sort_order
    ) AS colors
FROM
    wrapper_featured_colorways
JOIN afx_posts a ON
    (
        a.post_parent = wrapper_featured_colorways.designId
    )
JOIN afx_posts ON(
        afx_posts.ID = wrapper_featured_colorways.designId
    )
JOIN afx_postmeta z ON
    (
        (z.meta_key = 'svg_image') AND(a.ID = z.meta_value)
    )
JOIN wrapper_submodels ON wrapper_featured_colorways.submodel_id = wrapper_submodels.id
JOIN wrapper_models ON wrapper_submodels.model_id = wrapper_models.id
JOIN wrapper_chassis ON wrapper_models.chassis_id = wrapper_chassis.id
JOIN wrapper_manufacturers ON wrapper_chassis.manufacturer_id = wrapper_manufacturers.mf_id
JOIN wrapper_featured_colorway_colors ON wrapper_featured_colorways.id = wrapper_featured_colorway_colors.colorway_id
WHERE
    wrapper_featured_colorways.deleted = 0
GROUP BY
    wrapper_featured_colorway_colors.colorway_id

This is the Explain:

EXPLAIN SELECT
    wrapper_featured_colorways.id,
    wrapper_featured_colorways.design_name AS dname,
    wrapper_featured_colorways.designId,
    wrapper_featured_colorways.submodel_id,
    wrapper_featured_colorways.sled_image,
    wrapper_featured_colorways.sled_png,
    wrapper_featured_colorways.colorway_name,
    wrapper_featured_colorways.engineId,
    wrapper_featured_colorways.yearText,
    wrapper_submodels.model_id,
    wrapper_submodels.sub_name,
    wrapper_models.model_name,
    wrapper_chassis.id AS chassis_id,
    wrapper_chassis.chassis_name,
    wrapper_manufacturers.mf_name,
    wrapper_manufacturers.mf_id,
    a.guid AS svgPath,
    afx_posts.post_type,
    afx_posts.post_title,
    GROUP_CONCAT(
        wrapper_featured_colorway_colors.color_name
    ORDER BY
        wrapper_featured_colorway_colors.sort_order
    ) AS colors
FROM
    wrapper_featured_colorways
JOIN afx_posts a ON
    (
        a.post_parent = wrapper_featured_colorway[...]


1   SIMPLE  wrapper_manufacturers   
    NULL
    ALL PRIMARY 
    NULL
    
    NULL
    
    NULL
    4   100.00  Using temporary; Using filesort 
1   SIMPLE  wrapper_featured_colorways  
    NULL
    ALL PRIMARY 
    NULL
    
    NULL
    
    NULL
    45  10.00   Using where; Using join buffer (Block Nested Loop)  
1   SIMPLE  wrapper_submodels   
    NULL
    eq_ref  PRIMARY PRIMARY 4   fxgraphi_v2019.wrapper_featured_colorways.submodel_id   1   100.00  Using where 
1   SIMPLE  wrapper_models  
    NULL
    eq_ref  PRIMARY PRIMARY 4   fxgraphi_v2019.wrapper_submodels.model_id   1   100.00  Using where 
1   SIMPLE  wrapper_chassis 
    NULL
    eq_ref  PRIMARY PRIMARY 4   fxgraphi_v2019.wrapper_models.chassis_id    1   10.00   Using where 
1   SIMPLE  afx_posts   
    NULL
    eq_ref  PRIMARY PRIMARY 8   fxgraphi_v2019.wrapper_featured_colorways.designId  1   100.00  Using index condition   
1   SIMPLE  z   
    NULL
    ref meta_key    meta_key    576 const   139 100.00  Using where 
1   SIMPLE  a   
    NULL
    eq_ref  PRIMARY,post_parent PRIMARY 8   fxgraphi_v2019.z.meta_value 1   10.00   Using index condition; Using where  
1   SIMPLE  wrapper_featured_colorway_colors    
    NULL
    ALL 
    NULL
    
    NULL
    
    NULL
    
    NULL
    1431    10.00   Using where; Using join buffer (Block Nested Loop)  

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.