query monitor in wordpress shows slow query

I have a slow query on a wordpress site. I believe it’s my subquery inside it for the ‘abbreviation’. How can I rewrite this to make it faster? I’m not having much luck. The query takes about 0.8959 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 p1.guid as svgPath, p2.post_title, p2.ID, m1.meta_value as modelId, (select meta_value as abbreviation from afx_postmeta where afx_postmeta.meta_key = 'abbreviation'
AND afx_postmeta.post_id = p2.ID) as abbreviation
FROM afx_posts p1, afx_posts p2
INNER JOIN afx_postmeta m1
ON (p2.ID = m1.post_id
AND m1.meta_key = 'model_id')
WHERE p1.post_parent = p2.ID
AND p1.post_mime_type LIKE 'image%'
AND p2.post_type = 'design' and p1.ID = (select meta_value from afx_postmeta where afx_postmeta.meta_key = 'svg_image'
AND meta_value = p1.ID)
ORDER BY p2.post_date

This is the EXPLAIN:

EXPLAIN SELECT p1.guid as svgPath, p2.post_title, p2.ID, m1.meta_value as modelId, (select meta_value as abbreviation from afx_postmeta where afx_postmeta.meta_key = 'abbreviation'
AND afx_postmeta.post_id = p2.ID) as abbreviation
FROM afx_posts p1, afx_posts p2
INNER JOIN afx_postmeta m1
ON (p2.ID = m1.post_id
AND m1.meta_key = 'model_id')
WHERE p1.post_parent = p2.ID
AND p1.post_mime_type LIKE 'image%'
AND p2.post_type = 'design' and p1.ID = (select meta_value from afx_postmeta where afx_postmeta.meta_key = 'svg_image'
AND meta_value = p1.ID)
ORDER BY p2.post_date



1   PRIMARY p1  
    NULL
    ALL post_parent 
    NULL
    
    NULL
    
    NULL
    1081    11.11   Using where; Using temporary; Using filesort    
1   PRIMARY p2  
    NULL
    eq_ref  PRIMARY,type_status_date    PRIMARY 8   fxgraphi_v2019.p1.post_parent   1   14.25   Using where 
1   PRIMARY m1  
    NULL
    ref post_id,meta_key    post_id 8   fxgraphi_v2019.p1.post_parent   19  3.54    Using where 
3   DEPENDENT SUBQUERY  afx_postmeta    
    NULL
    ref meta_key    meta_key    576 const   102 10.00   Using where 
2   DEPENDENT SUBQUERY  afx_postmeta    
    NULL
    ref post_id,meta_key    meta_key    576 const   122 10.00   Using where 

Answer

well I tried to optimize the query a little bit as below , make sure I have not changed any logic ( I can’t test since I have no sample data)

also make sure you have index on afx_postmeta table on used columns (meta_value, post_id, meta_key). I’m saving the record I want from afx_postmeta in a cte so I won’t hit that table 3times later

  WITH tempPostmeta as (
        select 
            meta_value
            , post_id
            , meta_key
        from 
            afx_postmeta
        where 
            meta_key in ('abbreviation' , 'svg_image' , 'model_id')
);
    
    
    SELECT 
        p1.guid as svgPath
        , p2.post_title
        , p2.ID
        , m1.meta_value as modelId
        , p2_abbriviation.meta_value as abbreviation
    FROM 
        afx_posts p1
    INNER JOIN afx_posts p2
        ON p1.post_parent = p2.ID
        AND p1.post_mime_type LIKE 'image%'
        AND p2.post_type = 'design'
    INNER JOIN tempPostmeta p1_postmeta
        ON p1_postmeta.meta_key = 'svg_image'
        AND p1_postmeta.meta_value = p1.ID
    INNER JOIN tempPostmeta m1
        ON p2.ID = m1.post_id
        AND m1.meta_key = 'model_id'
    INNER JOIN tempPostmeta p2_abbriviation
        ON p2_abbriviation.meta_key = 'abbreviation'
        AND p2_abbriviation.post_id = p2.ID
    ORDER BY 
        p2.post_date

let us know how that improved your performance.