Relevanssi MySQL query running for a long time killing server

  • Hi there,

    I have used relevanssi for ages on my site which is a resource centre for documents and images without any problems. Users are able to log in, perform searches across custom post types and various custom taxonomies.

    I have 4 custom fields which can be toggled true or false (this is via Advanced custom fields) and this is presented to the user as toggles.

    Since adding the 4th one of these a day or two ago, I’m getting database connection issues, what appears to be happening is that a particular (relevanssi) query is taking a very long time and all other mysql connections queue up behind it, eventually I run out of connections and the site becomes unavailable.

    I’ve spoken to the server techies and they have said I should ask the plugin developer.

    The query that’s causing the issue is below. I’ve managed to replicate the issue by first running the query in PHPMyAdmin, and I can see that in status -> processes it’s taking a long time. Then I go to my WordPress admin and try and index relevanssi, I get stuck on ‘wiping index’ – nothing happens until I kill the process.

    ————————–

    
SELECT COUNT(DISTINCT(relevanssi.doc)) FROM wp_relevanssi AS relevanssi
LEFT JOIN wp_postmeta ON ( relevanssi.doc = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS mt1 ON (relevanssi.doc = mt1.post_id AND mt1.meta_key = 'is_obsolete' ) LEFT JOIN wp_postmeta AS mt2 ON ( relevanssi.doc = mt2.post_id ) LEFT JOIN wp_postmeta AS mt3 ON (relevanssi.doc = mt3.post_id AND mt3.meta_key = 'is_global' ) LEFT JOIN wp_postmeta AS mt4 ON ( relevanssi.doc = mt4.post_id ) LEFT JOIN wp_postmeta AS mt5 ON (relevanssi.doc = mt5.post_id AND mt5.meta_key = 'is_emea_only' ) LEFT JOIN wp_postmeta AS mt6 ON ( relevanssi.doc = mt6.post_id ) LEFT JOIN wp_postmeta AS mt7 ON (relevanssi.doc = mt7.post_id AND mt7.meta_key = 'is_usac' ) LEFT JOIN wp_postmeta AS mt8 ON ( relevanssi.doc = mt8.post_id ) WHERE relevanssi.term = relevanssi.term AND relevanssi.doc IN (
SELECT DISTINCT(tr.object_id)
FROM wp_term_relationships AS tr
WHERE tr.term_taxonomy_id IN (11)) AND (
( wp_postmeta.meta_key = '_members_access_role' AND wp_postmeta.meta_value = 'external' )
AND
(
mt1.post_id IS NULL
OR
( mt2.meta_key = 'is_obsolete' AND mt2.meta_value != '1' )
)
AND
(
mt3.post_id IS NULL
OR
( mt4.meta_key = 'is_global' AND mt4.meta_value != '1' )
)
AND
(
mt5.post_id IS NULL
OR
( mt6.meta_key = 'is_emea_only' AND mt6.meta_value != '1' )
)
AND
(
mt7.post_id IS NULL
OR
( mt8.meta_key = 'is_usac' AND mt8.meta_value != '1' )
)
) AND (
relevanssi.doc IN (
SELECT DISTINCT(posts.ID) FROM wp_posts AS posts
WHERE posts.post_type IN ('image')
) )

    The problem is, I’m not really sure what’s triggering this query let alone how to mitigate the effects of it stalling, so I’m reaching out for some guidance.

    Hope you can assist

    Kevin

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author Mikko Saari

    (@msaari)

    Yeah, that query is a killer. You’ve got the meta table LEFT JOINed nine times there. That must be painful to the server. Those meta filters must be done in a different way, that’s just not going to work.

    That query is the basic Relevanssi search query, so it’s triggered by each and every search someone makes on your site.

    I would recommend you drop the meta_query filters from the search, and instead do one relevanssi_hits_filter filter function that does the filtering. That would make the MySQL query much faster and lighter, and it would be easy to filter out the meta fields that way.

    Thread Starter the_lar

    (@the_lar)

    Hi Mikko,

    Thanks so much for coming back to me quickly! OK, I’ll have a look into refactoring things using that when I’ve got a bit of time to play with it. Just a question off the top of my head… relevanssi_hits_filter is obviously a standard filter, what action should I typically use to hook it to?

    Kevin

    Plugin Author Mikko Saari

    (@msaari)

    It’s the other way around: relevanssi_hits_filter is the filter hook, and you need to add your own filtering function to it. There are plenty of examples on relevanssi.com, just search for ”relevanssi_hits_filter”.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Relevanssi MySQL query running for a long time killing server’ is closed to new replies.