It probably will not optimize
My experience suggests that complex queries without group bys can be optimized. But as soon as you add a group by, that level of nesting becomes "opaque" to the query analyzer which then does the whole group by only to filter later.
The workaround that I've used for that is to stick inner inner queries in that create a condition that greatly reduces the size of that subselect. As you might imagine, this makes queries rather heinous with lots of duplicated information, so when I do this I typically also switch over to writing code to generate the SQL.
In fact that's usually how I handle complex queries. It is a lot easier to read code that produces SQL, and it is far easier to edit. With the query that I wrote above I might write something like this:
\nmy $formularyid_by_drugid_sql = qq{\n SELECT MIN(formularyid) AS formularyid\n FROM formulary\n GROUP BY drugid\n};\n\nmy $full_formulary_by_drugid_sql = qq{\n SELECT f.*\n FROM\n formulary f\n INNER JOIN (}\n . indent($formularyid_by_drugid_sql)\n . qq{ ) top\n ON f.formularyid = top.formularyid\n};\n\nmy $drug_and_formulary_isql = qq{\n SELECT *\n FROM\n drug d\n LEFT OUTER JOIN (}\n . indent($full_formulary_by_drugid_sql)\n . qq{ ) f\n ON d.drugid = f.drugid\n};\nAnd now I find the structure of the query somewht easier to interpret.
BTW this wasn't the best example of the technique. It makes more of a difference when you have multiple subqueries and/or interesting WHERE conditions on some subqueries.
Cheers,
Ben
