Slow query

[David Felfoldi]

Hi the following query is just killing my database server it takes more than 300 seconds to finish:

SELECT DISTINCT(pm.meta_value) FROM wp_postmeta pm
    LEFT JOIN wp_posts p
    ON pm.meta_value = p.ID
    WHERE p.post_type IN ('tribe_events')
    AND pm.meta_key LIKE '\_tribe\_%\_for\_event'
    AND pm.meta_value IS NOT NULL;

My wp_postmeta has about 600k records so my guess is that the DISTINCT and LIKE operators are just performance killers due to the number of records it needs to search.

Any ideas on how to fix this?

[David Felfoldi]

Some extra data;

Query as it is, using disctinct, returns 87 records, takes over 300s to finish.

Query without disctinct, returns 94 recors, takes 30s to finish, and it also gets cached.

That use of disctinct there does not seem to be appropriate.

[David Felfoldi]

Some extra data;

Query as it is, using DISTINCT, returns 87 records, takes over 300s to finish.

Query without DISTINCT, returns 94 recors, takes 30s to finish, and it also gets cached.

That use of DISTINCT there does not seem to be wise.

[Barry]

Sorry to hear you’ve been hitting up against problems, David.

I’d love to narrow this down a little more. Are there specific screens, views or pages where you notice these queries running?

If you are using a tool like Query Monitor to capture this information what component (ie, which plugin) does it reference as the source of those queries?

Thanks!

[David Felfoldi]

Hi we notice that this gets triggered for example when going to /wp-admin/edit.php?post_type=tribe_events

[Barry]

OK, thanks for confirming – I see what you mean.

There aren’t any particularly straightforward ways of mitigating this without hacking core plugin code, which I’d be keen to avoid if possible.

add_filter( 'views_edit-tribe_events', function( $pass ) {
	$query_filter = function( $query ) {
		global $wpdb;

		if ( false !== strpos( $query, "'\_tribe\_%\_for\_event'" ) ) {
			$query = "'SELECT * FROM $wpdb->posts WHERE 1 = 0";
		}

		return $query;
	};

	add_filter( 'query', $query_filter );
	return $pass;
}, 5 );

The above snippet could be added either to a custom plugin (preferred) or else to your theme’s functions.php file (I didn’t test that however and am not positive that it will work if placed in there, but you could try if this approach is easier for you).

If you’re comfortable doing so, could you try getting that in place? It will have a side effect of making the “Ticketed (8)”-type link that you may see along side at the top of that particular admin screen inaccurate but might just resolve the worst parts of this performance issue and at least give you easier access to that area.

Does that work for you, at least as a short term fix?

[Support Droid]

Hey there! This thread has been pretty quiet for the last three weeks, so we’re going to go ahead and close it to avoid confusion with other topics. If you’re still looking for help with this, please do open a new thread, reference this one and we’d be more than happy to continue the conversation over there.

Thanks so much!
The Events Calendar Support Team

[Author]

Posts