[Resolved] Slow query

[Author]

Posts

[Alessandro]

As mentioned in this solution
https://toolset.com/forums/topic/taxonomy-direct-links-in-mixed-taxonomies/

I integrated this feature in my listing site.

The problem is that it is very sloowww

Can you help me to find another way to have a better query?

[Nigel]

I suspect it may be inevitable that the page in question is slow.

That page includes not just one View, but all of the Views from the linked pages, so all of those queries have to be run (for the conditional check on whether the View produces any results or not).

I don't think in this scenario where you are passing attributes to the View to filter by that the View can benefit from caching, and they would need re-running for every page load.

You could add the Query Monitor plugin to your site and check how many queries are run on the page coming from Views. That will help show how big the problem is in terms of how many Views are being run on the page, but it won't help with a solution.

I think the only solution would effectively be to build your own caching solution for these Views with shortcode attributes, so that instead of the conditional testing the View results directly, it tested the custom cached version you maintain.

That would be fairly challenging and require custom coding. It may be necessary to recruit a developer to implement it (see https://toolset.com/contractors/).

Otherwise you could remove the conditional checks for links to pages that don't return results and instead show all of the links.

[Alessandro]

Ok ... I think a solution could be to build this via custom code if you can help me. I'd like to integrate it like in filtering way toolset use.

Or can you see if this approach could be useful ?
https://wordpress.stackexchange.com/questions/97923/combine-two-taxonomies-in-a-hierarchical-tree

[Jamal]

The second approach is basically similar to the discussed views approach, maybe slightly faster(or more faster if the view queries custom field from the database). Basically a view uses an underlying SQL query that is generated using WP_Query for post views, or WP_Term_Query for taxonomy views:
- https://developer.wordpress.org/reference/classes/wp_query/
- https://developer.wordpress.org/reference/classes/wp_term_query/

Whenever a view is rendered or used inside a loop, it will generate an SQL query. Assuming that you have n elements from the first taxonomy and m elements from the second taxonomy, you will end up with (2 + n * m) queries:
- 1 query to get the elements of the first taxonomy.
- 1 query to get the elements of the second taxonomy.
- n * m queries for each combination of terms.
Please note that this count is very optimistic and is true only if the solution is built entirely though the second approach. If implemented using views, additional queries will be run for each condition.

To optimize the second approach, you must implement some kind of caching. For example, at the bottom of the function, just before the "return $output;", you can save the $output variable on a transient. For example, set_transient( 'double_term_tree', $output, 12 * HOUR_IN_SECONDS ), to cache this menu for 12 hours.
https://developer.wordpress.org/apis/handbook/transients/

Then, you can create a custom shortcode that will check if the transient still exists on the database. If it exists it will return it. If it does not, it will return the "double_term_tree" function call.

// Inside shortcode body
if ( false === ( $output = get_transient( 'double_term_tree' ) ) ) {
    return double_term_tree( 'product', 'brand', 'category' );
}
return $output

Transients have a timeframe. If you create/update/delete posts too often, you will need to purge the transient programmatically after a post is (un)assigned to a taxonomy term.

I hope this makes sense. However, this is not a simple custom code solution that we can help with. If you are not comfortable with programming, consider hiring a developer (see https://toolset.com/contractors/).

[Jamal]

Hello there! My apologies, I miss read the second approach code and I though it is similar to how views would do it.
https://wordpress.stackexchange.com/questions/97923/combine-two-taxonomies-in-a-hierarchical-tree

This approach is actually lighter, as it will have only 3 queries:
- 1 query to get the elements of the first taxonomy.
- 1 query to get the elements of the second taxonomy.
- 1 query to get all the posts. It will use more memory as it will go through all the posts on your custom post type(s).

You can integrate it by integrating the function in a shortcode shortcode:

// Add Shortcode
function double_term_tree( $atts ) {
	// Attributes
	$atts = shortcode_atts(
		array(
			'first' => 'category',
			'second' => 'post_tag',
		),
		$atts
	);
        // call the function here,  or implement caching with transient to make it more efficient
        return double_term_tree( 'product', $atts['first'], $atts['second] );
}
add_shortcode( 'double-term-tree', 'double_term_tree' );