Excessive table joins and poor performance

Wonder if we could do this by something like

CREATE TABLE lookup
(
id INT NOT NULL,
tid INT,
parent INT,
PRIMARY KEY USING BTREE (id)
) ENGINE = MEMORY;

- CREATE TEMPORARY TABLE tree_results

INSERT INTO tree_results from lookup ?

AAAh hang on, i suspect this is a load issue generated by the http://drupal.org/project/taxonomy_filter module

Ok, reanimating this.

tf_count_nodes_by_terms() consuming all server (mysql) resources with {node} having ~18,000, and {term_node} having 53,782 rows, on a dedicated server with moderate resources. Load averages almost tripled after adding "Context" blocks to taxonomy/term pages, and yes we have many listing pages all rely on taxonomy.

This module is marvellous, but many inner joins ornated with count(distinct) really consumes everything. Loosing page cache is our most feared problem now. By the way, I added "nid_tid", and "tid_nid" indexes manually (blindly) for {term_node} and situation seems to be soothed noticeably, though I am not sure if this is not a placebo.

Tough similar (and very fast), we do not want to use our ApacheSolr's taxo support, as we have customised taxo pages heavily with panels and views, and of course fell in love with Taxo filter that, we solved all contextual drill-down problems "in-style" just by adding few blocks.

Can we hope any performance improvements in the future?

Tx.

Drupal 6.13, min cache 1hr, JS aggregator with Jsmin+, optimized CSS
MySQL (5.1.36) MyISAM. Query_cache, key_buffers, join_buffers etc. are all tuned for best effort.
Centos 5.3 (32bit)
PHP 5.2.10
Xcache opt code, memcached object cache with libmemcached+PECL-memcached.

You have hit on the Achilles heel of the submodules for taxonomy filter with the term depth you describe. I appreciate your thoughts on how to improve the performance and am open to suggestions.

I am curious what kind of performance you would have if you used the base module instead of the context module. I believe the base module avoids the calls to tf_count_nodes_by_terms() and hopefully has better performance. Would you try it simply for the performance metrics and respond here? Would it work for your site? Why or why not?

Also, would you post a link to the site so I could see the context menu in action?

By the way we are not using deep hierarchies but we have quite many terms. You can try this page for example. Site is not in English but is self explanatatory. You can also click to the top menu items with small arrows, which expand and expose list of taxonomy terms. As you might see, we are using taxonomy terms for all navigation purposes, not just tagging.

Site is renewed and nearly 5% of the nodes have properly tagged, and only half of the terms are in place yet, even this can cause heavy loads, especially when SE bots are crawling.

We use heavy caching but as you might agree, it is impossiple to cache every possible combination of pages with a crowded taxonomy. What's more, our site is updated so frequently that few dozen nodes with new tags are introduced daily, that expires most of the cached objects. Also context is absolutely needed because of our site's dynamic and quite complex structure. Just listing all terms with the base is not adequate for us, as 95% of the list will be irrelevant and will yield empty lists.

I am not a real PHP or SQL coder, but I can make some educated guesses anyway.

As I have said, indexes seem to help though I am not sure. On a second thought, at least for the ones not using item counts near terms, COUNT (may be DISTINCT also) can be eliminated and some sort of LIMIT = 1 functionality can be added to queries just to check if the terms are present together or not. I hoped the relevant setting in the admin page would yield this, but tf_count function have not been used this way. First hit is enough for the term to be in the contextual list anyway, but again I am not sure if this might help :/

I dig into SQL documents a bit and hit delayed joins, but people say that at least new versions of MySQL does handle inner joins better than this method, and this is not applicable to all sorts of join queries anyway.

I am rather sure some sort of optimization can be done, but that's only a feeling as I am not up to deep secrets of coding much. I can not make a precise profiling on a live and busy site, but I can say context nearly triples MySQL loads on busy hours, causing long waits though does not crash anything.

Our server will be changed with a more muscled one in a short time, but brute force is not a real solution. As you might share, any possible headroom is invaluable in this business, matter is not just to keep up with the load.

Tx for listening and for your efforts.

You wrote:

Also context is absolutely needed because of our site's dynamic and quite complex structure. Just listing all terms with the base is not adequate for us, as 95% of the list will be irrelevant and will yield empty lists.

Although the name is not 'context,' the default behavior of the base module is to omit a menu item for terms that do not match any nodes. Menu items could be included but this requires the user to check the box:

"Display a menu item for terms that do not match any nodes."

on the admin/settings/taxonomy_filter/%vid/edit page.

Again, I am curious how the base module would work in this situation and if it would help with performance.

Yes, you are absolutely right. In fact base fuctionality is what we have enoghly needed. I am not sure why we explicitly determined to use context. Lack of investigation due to tight schedule I am afraid. "Context" name seems to be more contextual by the way :) and we stuck somehow.

Anyway, base seems to be cheaper on sql (at least not using tf_count), though queries resembles each other but everted. Perhaps not jailing the results till last bit, but using GROUP BY helps also. Now SQL load does not triple but seems to be doubled when TF is enabled on busy hours, but this seems to be quite normal.

~ 25-30% of SQL load seems to be freed with the base, and filtering seems to be quicker, but live server is hard to profile for one aspect and this may be rather subjective, thus should be taken with a grain of salt.

I think that's what we should expect from TF for now. In fact I dig more on query optimization recently, but suspected query seems to be unreducible and minimal enough.

...but in my opinion COUNT(DISTINCT) still can be conditionally supressed according to the relevant setting (in fact I deleted manually :S).

Thank you for your assist, and any milliseconds are welcome :)

Too many JOINs and poor performance:

Problem cause:
tanxonomy_filter.module - row 462

Example result:
SELECT td.vid, td.tid, td.name, COUNT(DISTINCT(n.nid)) AS count FROM term_data td INNER JOIN term_node tn ON tn.tid = td.tid
INNER JOIN term_node tn0 ON tn.nid = tn0.nid INNER JOIN term_node tn1 ON tn.nid = tn1.nid INNER JOIN node n ON tn.nid = n.nid
INNER JOIN ....INNER JOIN ....INNER JOIN ....INNER JOIN ....INNER JOIN ....INNER JOIN ....INNER JOIN ....
WHERE td.vid IN (1) AND tn0.tid IN (169) AND tn1.tid IN (92) AND tn2.tid IN (.....
AND n.status = 1 AND n.moderate = 0 GROUP BY td.tid ORDER BY vid, weight, name

...and bug fixing:

Example result:
SELECT td.vid, td.tid, td.name, COUNT(DISTINCT(n.nid)) AS count FROM term_data td INNER JOIN term_node tn ON tn.tid = td.tid
INNER JOIN term_node tn0 ON tn.nid = tn0.nid INNER JOIN node n ON tn.nid = n.nid
WHERE td.vid IN (1)
AND tn0.tid IN (169, 92, ...,...,..,...,...,0)
AND n.status = 1 AND n.moderate = 0 GROUP BY td.tid ORDER BY vid, weight, name

(Sorry, I can't made patch yet.)

Thanks for posting on this. I think you are on the right track. I ran your code alongside the current code. They produce different results. Would you try the same on your site and see if you get different results?

Would you provide some metrics about the site having the performance issue -- number of vocabularies, number of terms, number of nodes, etc.? How long do the queries take?

Also, you might want to use the dev version.