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
