[development] [Drupal7] Efficient to query nodes from multiple terms (CCK)

[Pierre Rineau]

On Sun, 2011-02-27 at 11:34 +0800, Ryan Chan wrote:
> I am development a module that show a listing of node.
> 
> Currently, the node of ap specific content type has multiple CCK term
> references to a specify vocabulary, i.e.  v1, v2, v3
> 
> I want to list of nodes if they matched a specify term conditions, e.g.
> 
> v1=tid1
> v1=tid1, v2=tid2
> v1=tid1, v2=tid2, v3=tid3
> v1=tid1, v3=tid3
> 
> ...
> 
> 
> I am using subquery to query the above condition, but I am not sure if
> any better method to speed up my query?
> 
> 
> Any suggestion for my usage?

The only suggestion I would give you is don't be afraid of complex SQL
queries, the only important thing is to always use direct (join|where|
etc) conditions over indexed fields.

Sometime subqueries can speedup a complex query, not always, test more
than one solution using EXPLAIN (and ANALYZE if you're going postgresql)
it will show you the real query complexity (as the database engine
understand and compile it). Sometime only changing a where or a join
order can give you significant boost.

Pierre.