Taxonomy Terms + Views brings site to its knees
ulfk - July 24, 2008 - 06:04
I have approx 10 vocabularies, with upto 15 terms each. Adding each to a views filter results in the page loading forever without every providing a result. The devel module shows a query like:
SELECT DISTINCT(node.nid) FROM {node} node LEFT JOIN {term_node} term_node ON node.nid = term_node.nid LEFT JOIN {term_hierarchy} term_hierarchy ON term_node.tid = term_hierarchy.tid LEFT JOIN {term_node} term_node2 ON node.nid = term_node2.nid LEFT JOIN {term_hierarchy} term_hierarchy2 ON term_node2.tid = term_hierarchy2.tid LEFT JOIN {term_node} term_node3 ON node.nid = term_node3.nid LEFT JOIN {term_hierarchy} term_hierarchy3 ON term_node3.tid = term_hierarchy3.tid LEFT JOIN {term_node} term_node4 ON node.nid = term_node4.nid LEFT JOIN {term_hierarchy} term_hierarchy4 ON term_node4.tid = term_hierarchy4.tid LEFT JOIN {term_node} term_node5 ON node.nid = term_node5.nid LEFT JOIN {term_hierarchy} term_hierarchy5 ON term_node5.tid = term_hierarchy5.tid LEFT JOIN {term_node} term_node6 ON node.nid = term_node6.nid LEFT JOIN {term_hierarchy} term_hierarchy6 ON term_node6.tid = term_hierarchy6.tid LEFT JOIN {term_node} term_node7 ON node.nid = term_node7.nid LEFT JOIN {term_hierarchy} term_hierarchy7 ON term_node7.tid = term_hierarchy7.tid LEFT JOIN {term_node} term_node8 ON node.nid = term_node8.nid LEFT JOIN {term_hierarchy} term_hierarchy8 ON term_node8.tid = term_hierarchy8.tid LEFT JOIN {term_node} term_node9 ON node.nid = term_node9.nid LEFT JOIN {term_hierarchy} term_hierarchy9 ON term_node9.tid = term_hierarchy9.tid LEFT JOIN {term_node} term_node10 ON node.nid = term_node10.nid LEFT JOIN {term_hierarchy} term_hierarchy10 ON term_node10.tid = term_hierarchy10.tid LEFT JOIN {term_node} term_node11 ON node.nid = term_node11.nid LEFT JOIN {term_hierarchy} term_hierarchy11 ON term_node11.tid = term_hierarchy11.tid WHERE (node.status = '1') AND (node.type IN ('story')) AND (term_node.tid IN ('0','17','18','19','20','21','22','23','24','25','26','27')) AND (term_node2.tid IN ('0','93','94')) AND (term_node3.tid IN ('0','29')) AND (term_node4.tid IN ('0','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48')) AND (term_node5.tid IN ('0','51','50','53','52','54','55')) AND (term_node6.tid IN ('0','59','60','61','62','63','56','57','64','66','65')) AND (term_node7.tid IN ('0','68','67')) AND (term_node8.tid IN ('0','71')) AND (term_node9.tid IN ('0','72','73','74','75','77')) AND (term_node10.tid IN ('0','2','3','4','5','6','7','12','13','14','15','11','8','9','10')) AND (term_node11.tid IN ('0','85','84','82','83','78','87','91','81','79','80','88','89','90')) GROUP BY node.nidIs anyone experiencing this issue? Taxonomy is is integral to Drupal, I suspect that if this was a general issue it would have been experienced and corrected already. So its probably specific to my site?
Yikes
That's about the most frightening SQL query I've ever seen haha :P Just all those Joins after Joins after Joins - 22 of them (not to mention the rest) is likely to slow anything to a crawl.
Hmm what exactly are you doing in your View? Is this just core Taxonomy module and Views module? Or do you have other taxonomy-related modules mixed in with this?
Where in Devel are you getting the query by the way? (just the query log at the bottom of the page? I just can't get any view to output anywhere near that big of a query haha, so not sure if I'm looking at the same thing as you).
-- David
absolutecross.com
Trying to create a CCK field search form
Yes, that is a monster of a query. I have all the devel modules enabled, and one of them provides extra tabs (alongside View, Edit, etc) that display the variables, queries, etc. - so thats where it came from. This has been driving me nuts, and this is the result of starting with a fresh install, enabling only Categories and Views. Populating the categories, creating 3 - 5 test nodes, and then building the query.
So the culprit is either Categories or Views. Initially, I thought it might be Views, but now I remembered that when I was using CCK option fields, a similar view worked fine. Only when using the Categories + Views is when I encounter this.
Looks like I'll have to create my 'advanced' search' (search by CCK fields) form using something else.
Views + Taxonomy Terms is a known issue
Looks like this is a long-standing issue - http://drupal.org/node/77543