BuddyDev

Search

[Resolved] Slow Queries

Tagged: 

  • Participant
    Level: Enlightened
    Posts: 27
    Simon Parker on #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: 19855
    Brajesh Singh on #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 #22358

    Thanks Brajesh, will look forward to the update.

  • Keymaster
    (BuddyDev Team)
    Posts: 19855
    Brajesh Singh on #22365

    Thank you. Will update here.

  • Keymaster
    (BuddyDev Team)
    Posts: 19855
    Brajesh Singh on #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