Hi!

I have just noticed something very strange. I have a SELECT which lists all
records from a master table and a sum from a detail table. Something like:

SELECT m.key, m.name, m.creation_date, m.note, m.group, SUM(d.amount)
FROM master m
LEFT JOIN detail d ON d.master_key=m.key
GROUP BY m.key, m.name, m.creation_date, m.note, m.group

Now in my application I will always select only those rows from master which
belong to a particular group. Something like:

SELECT m.key, m.name, m.creation_date, m.note, m.group, SUM(d.amount)
FROM master m
LEFT JOIN detail d ON d.master_key=m.key
WHERE m.group=1234
GROUP BY m.key, m.name, m.creation_date, m.note, m.group

This select performs in about 0.0017 seconds on my system, in other words,
it is fast enough. To make my code easier to read I wanted to wrap the
original select in a view and then select from the view:

CREATE VIEW my_view (key, name, creation_date, note, group, total_amount) AS
[the_select_at_the_top]

SELECT key, name, creation_date, note, group, total_amount
FROM my_view
WHERE group=1234

Unfortunatelly, this takes some six seconds to process (about 600x as long
as the select). It seems likely Firebird first counts the amounts over the
whole table and then applies the group=1234 condition. My question is, is
there anything I can do to keep the simplicity of a view but improve the
speed? The plans look pretty much the same to me:

PLAN SORT (JOIN (MASTER NATURAL,DETAIL INDEX (DETAIL_KEY)))
PLAN SORT (JOIN (MY_VIEW MASTER NATURAL,MY_VIEW DETAIL INDEX (DETAIL_KEY)))

All this on WinXP, Firebird 1.5 running in SuperServer mode.

And a somewhgat unrelated minor question: Do I really have to list all
selected fields in the GROUP BY clause? This is one thing I rather like
about MySQL, that I can simply group by the primary key (m.key) and not type
the other fields which obviously depend on that primary key...

Thanks,

Pepak