At 08:37 PM 06-08-02 +0000, you wrote:

>Hi, i have about 400,000 records on my table Cliente and about
>400,000 on
>Cliente_Mailing and when i run this query:
>
>Select Cliente.Nome
> From Cliente,Cliente_Mailing Where
> (Cliente_Mailing.id_mailing=9 or Cliente_Mailing.id_mailing=8)
> and Cliente.id_cliente=Cliente_Mailing.id_cliente
> Order by nome
>
>It Takes about 24 minutes
>
>But if i test only Cliente_Mailing.id_mailing=9 like this:
>Select Cliente.Nome
> From Cliente,Cliente_Mailing Where
> (Cliente_Mailing.id_mailing=9)
> and Cliente.id_cliente=Cliente_Mailing.id_cliente
> Order by nome
>
>it takes 1 second
>
>Why and how to speed the top query

1. What are the metadata attributes of Cliente.id_cliente and
Cliente_Mailing.id_cliente? If they are not primary keys, do you have
indexes on them? If they should be primary keys, why aren't they?

2. You have a potential ambiguity in the ORDER BY clause of both queries.

btw, using explicit join syntax probably won't be faster but it will
certainly be clearer:

Select Cliente.Nome
From Cliente
JOIN Cliente_Mailing
ON
Cliente.id_cliente=Cliente_Mailing.id_cliente
WHERE
Cliente_Mailing.id_mailing=9 or Cliente_Mailing.id_mailing=8
Order by CLIENTE.nome

heLen

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