[Drupal7] Efficient to query nodes from multiple terms (CCK)
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? Thanks.
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.
An important thing to remember is that what looks complex to you is not necessarily complex to the database. Many people are scared to death of JOINs because they think that makes the statement complex, while, in reality, they often make the statement simpler to the database. Just make sure you (the coder) know the difference between INNER and LEFT JOINs. And some times, a subquery is the only way to get accurate results. For example, getting a count of matching items from one table based on a selection from another is generally going to give you erroneous (and much slower) results without a subquery. And I echo the "test more than one solution" sentiment. I usually will pop over to PhpMyAdmin and test a query first to make sure it gives me what I expect. There I can also break out subqueries (and JOINs) to see if they give me what I expect. This is much better than the customer coming back and saying, "Are you sure this screen is right?" Nancy Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr. ________________________________ From: Pierre Rineau 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
Also, subqueries in MySQL are slow only in the WHERE or HAVING clauses, because they run once for each possible record. That is only sometimes a problem in practice. In a FROM clause, however, they're perfectly fine and can be a very good way to "pre filter" a table, as they will only run once. --Larry Garfield On Sunday, February 27, 2011 1:19:22 pm nan wich wrote:
An important thing to remember is that what looks complex to you is not necessarily complex to the database. Many people are scared to death of JOINs because they think that makes the statement complex, while, in reality, they often make the statement simpler to the database. Just make sure you (the coder) know the difference between INNER and LEFT JOINs.
And some times, a subquery is the only way to get accurate results. For example, getting a count of matching items from one table based on a selection from another is generally going to give you erroneous (and much slower) results without a subquery.
And I echo the "test more than one solution" sentiment. I usually will pop over to PhpMyAdmin and test a query first to make sure it gives me what I expect. There I can also break out subqueries (and JOINs) to see if they give me what I expect. This is much better than the customer coming back and saying, "Are you sure this screen is right?"
Nancy
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
________________________________
From: Pierre Rineau 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
Hello all, On Mon, Feb 28, 2011 at 3:31 AM, Larry Garfield <larry@garfieldtech.com> wrote:
Also, subqueries in MySQL are slow only in the WHERE or HAVING clauses, because they run once for each possible record. That is only sometimes a problem in practice. In a FROM clause, however, they're perfectly fine and can be a very good way to "pre filter" a table, as they will only run once.
--Larry Garfield
The reason I ask this because I believe my usage is quite common? Shouldn't someone also need the execute similar queries? Maybe even in the Drupal core I am not aware of. I just wonder if any existing codes can be used. Thanks.
participants (4)
-
Larry Garfield -
nan wich -
Pierre Rineau -
Ryan Chan