BuddyDev

Search

[Resolved] Slow Queries

Tagged: 

  • Participant
    Level: Enlightened
    Posts: 27
    Simon Parker on April 17, 2019 at 10:07 am #22351

    I have been trying to figure out a few bugs causing my site to go slow.

    One repeated slow query that seems to happen on every page

    SELECT COUNT( DISTINCT p.ID ) AS total
FROM wp_posts AS p
INNER JOIN wp_postmeta AS pm
ON p.ID = pm.post_id
WHERE p.post_type= 'mpp-gallery'
AND p.post_status = 'publish'
AND pm.meta_key='_mpp_component_id' and pm.meta_value=1
AND ID IN (SELECT DISTINCT t.object_id
FROM (SELECT DISTINCT tr.object_id AS object_id 
FROM wp_term_relationships as tr
WHERE tr.term_taxonomy_id IN ('37723') 
UNION ALL
SELECT DISTINCT tr.object_id AS object_id 
FROM wp_term_relationships as tr
WHERE tr.term_taxonomy_id IN ('38823', '38821', '37722', '37719', '37721') 
UNION ALL
SELECT DISTINCT tr.object_id AS object_id 
FROM wp_term_relationships as tr
WHERE tr.term_taxonomy_id IN ('37724') ) AS t
GROUP BY object_id
HAVING count(*) >=3 )

    Is this necassary to happen on every page. Could it use a transient?

  • Keymaster
    (BuddyDev Team)
    Posts: 24623
    Brajesh Singh on April 17, 2019 at 10:09 am #22352

    Hi Simon,
    Thank you for sharing. It can certainly be done using transient. Please allow me 2-3 days to audit all our count queries.

    Thank you
    Brajesh

  • Participant
    Level: Enlightened
    Posts: 27
    Simon Parker on April 17, 2019 at 10:33 am #22358

    Thanks Brajesh, will look forward to the update.

  • Keymaster
    (BuddyDev Team)
    Posts: 24623
    Brajesh Singh on April 17, 2019 at 2:58 pm #22365

    Thank you. Will update here.

  • Keymaster
    (BuddyDev Team)
    Posts: 24623
    Brajesh Singh on April 25, 2019 at 8:36 am #22584

    Hi Simon,
    Thank you for the patience.
    I have added caching the count to user meta for 2 context(user’s themselves and in non logged in context).

    The above query will still run when a logged in user visit another user’s profile. We are unable to add caching as the context is not very clear(and can vary based on activated plugins).

    The current tweak should help resolve the issue greatly as the counts are only done once unless the actual gallery count changes for the logged in/logged out user).
    https://github.com/buddydev/mediapress/commit/bf65c059e3019fb616d7e4cff4be841e740e3f5e

    I am working on some more updates before pushing this change to wp.org.

    Regards
    Brajesh

The topic ‘ [Resolved] Slow Queries’ is closed to new replies.

This topic is: resolved