in reply to 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.

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

MySQL 4 offers some hope, though, with transactions. You could create a temporary table, fill it using the SELECT MIN() operation, and then LEFT JOIN that into your main query, finally delete the table and lastly ROLLBACK the transaction.

Either way it is going to get ugly! Good luck!

Replies are listed 'Best First'.
Re^2: Query rewriting question
by dragonchild (Archbishop) on Oct 19, 2005 at 13:29 UTC
    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?
[reply]

In Section Seekers of Perl Wisdom