At 05:45 PM 08-08-02 -0300, you wrote:

>The problem is when i use a "regular" sql
> select count(*) as total from clients
> where (clientes.id=10) OR (clientes.id=11)
>
>interbase take too long.
>So i change the query to speed up.
>
>Here is the original:
>Select Count(*) Total From Cliente C,Cliente_Mailing CM ,Mailing_Campanha MC
>Where C.id_cliente=CM.id_cliente and CM.id_mailing=MC.id_mailing and
>MC.id_campanha in(8,20)
>
>This is a very slow query. I had same problems with other querys (wich didnt
>need sum) where Union solve the problem.

You can't aggregate across unions.
Your original query uses two slow operations (Count(), which walks through
the set row by row, and IN(), which doesn't use indexes). It also has a
three-way join which will be slow if any of the join columns doesn't have
an index.

What about

Select Count(*) asTotal From Cliente C
join Cliente_Mailing CM on C.id_cliente=CM.id_cliente
join Mailing_Campanha MC on CM.id_mailing=MC.id_mailing
Where MC.id_campanha = 8
or MC.id_campanha = 20
?

heLen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
______________________________________________________________________