> Yes, it is intentional, in fact, here I used the EntryDate field, but
> I can use many other fields, most of them not indexed. The user can
> filter by any field, there are many scenarios. The only useful index
> must be OrderDate because all consults use always this field. Then,
> additionally the user can exclude some rows according his criteria.
> Here is when I have the problem, the user wants to exclude for example
> customers from Canada, and FB provides a suboptimal plan instead of
> using the OrderDate index.

Without adding some indexes on the Customer table you will always have
problems.

The reality is that the Customer table is much smaller than the Orders
table (in many systems by an order of magnitude); as such the optimizer
is likely to want to "re-phrase" the query to something like:

select sum(Orders.units)
from Customers
join Orders on Orders.CustomerId=Customers.CustomerId
where
Orders.OrderDate between `2007/1/1' and `2007/12/31'
and Customers.EntryDate between `2006/1/1' and `2006/12/31'
and Customers.CustomerType=1

It is doing this because it believes that scanning the customer table is
the faster approach -- and in 99% of the case, it is right!

By adding indexes to "high probability of use" fields (in single and
compound indexes as appropriate) of the Customer table, you will allow
the engine to narrow the number of customers quickly and thus
significantly reduce query execution time.


Sean

P.S. It is the "high probability of use" factor which turns database
design from a science into an art, as "high probability of use" is a
function of your application and how your users think of problems.