Query rewriting question

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

A CGI::Application based app that I'm developing for a client uses the following query:

SELECT FirstName, LastName, PhoneNumber, members.MemberID AS MemberID
FROM members
LEFT JOIN phone_numbers ON 
(
members.MemberID=phone_numbers.MemberID
AND phone_numbers.PhoneNumberID IN (SELECT MIN(PhoneNumberID) FROM pho
+ne_numbers GROUP BY MemberID)
)
WHERE Verified=1
ORDER BY LastName, FirstName, members.MemberID;
[download]

Unfortunately, their host is using MySQL 4.0, which doesn't support subqueries (subqueries were introduced in MySQL 4.1). Can this query be rewritten as a single query without subqueries, or should I take some of the logic out of the query and rewrite it in Perl?

Comment on Query rewriting question Download Code

Replies are listed 'Best First'.
Re: Query rewriting question by dragonchild (Archbishop) on Oct 19, 2005 at 03:19 UTC
All subqueries can be rewritten as a JOIN. That's part of the relational calculus. Most rewrites are very ... difficult to maintain. I'd urge their host to upgrade to 4.1. Now, your subquery can be removed because it's a constant value. And, in fact, I would remove it and do that query separately, even with subqueries, because it would almost always be more efficient that way. My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]
Re: Query rewriting question by davidrw (Prior) on Oct 19, 2005 at 02:19 UTC
You can just do the subquery in perl first: my $min_phone_ids = $dbh->selectcol_arrayref("SELECT MIN(PhoneNumberID +) FROM phone_numbers GROUP BY MemberID"); my $sql = <<EOF; SELECT FirstName, LastName, PhoneNumber, members.MemberID AS MemberID FROM members LEFT JOIN phone_numbers ON members.MemberID=phone_numbers.MemberID EOF $sql .= " AND phone_numbers.PhoneNumberID IN (" . join(',','?' x @$mi +n_phone_ids). ") " $sql .= <<EOF; WHERE Verified=1 ORDER BY LastName, FirstName, members.MemberID EOF $dbh->selectall_arrayref( $sql, {}, @$min_phone_ids ); [download] is your join logic correct? As i read it you're checking if the phone id is the same as the min for _any_ member, and not necessarily the min for _that_ member...	[reply] [d/l]
Re: Query rewriting question by Anonymous Monk on Oct 19, 2005 at 02:18 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. 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!	[reply] [d/l] [select]
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: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]