in reply to Re: Query rewriting question
in thread Query rewriting question

MySQL is an incredibly frustrating database when it comes to joins.. for whatever reason it seems to crawl to a halt when it comes to putting two tables together. So, in general, writing two seperate queries with Perl logic in between is often faster.

I wrote a reporting application that would join several million-row tables with complex WHERE clauses that returned back in, generally, under a second. Maybe your schema isn't designed with speed in mind?

Because you are doing a SELECT MIN() operation it is almost a guarantee that you cannot write this as a single query.

No. First, the inner query doesn't refer back to the outer query, so it's actually a constant that can be factored out. Second, every subquery can be rewritten as a join. Granted, you might have to do some funky HAVING and GROUP BY stuff, but it's possible.

My criteria for good software:
  1. Does it work?
  2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

In Section Seekers of Perl Wisdom