More info:

Well, this query worked very fast:

Select Cliente.Nome,Cliente_Mailing.* From Cliente join Cliente_Mailing
on Cliente.id_cliente=Cliente_Mailing.id_cliente
Where Cliente_Mailing.id_mailing >= 8 and Cliente_Mailing.id_mailing
<= 9
But the id_mailing will not be sequence. It could be: id_mailing
in(13,322,422,...)

Query Results (800 records):
Select Cliente.Nome
From Cliente,Cliente_Mailing Where
(Cliente_Mailing.id_mailing in(9,8))
and Cliente.id_cliente=Cliente_Mailing.id_cliente
Order by nome

**** Structure ****
CREATE TABLE CLIENTE (
ID_CLIENTE INTEGER NOT NULL,
NOME VARCHAR (40) NOT NULL,
ENDERECO VARCHAR (50),
NUMERO VARCHAR (10),
COMPLEMENTO VARCHAR (10),
OPERADORA VARCHAR (3),
DDD VARCHAR (5),
TELEFONE VARCHAR (17),
FAX VARCHAR (22),
EMAIL VARCHAR (60),
CELULAR VARCHAR (17),
CPF VARCHAR (14),
CGC VARCHAR (18),
PESSOA VARCHAR (1),
CIDADE VARCHAR (35),
UF VARCHAR (2),
CEP VARCHAR (9),
ATIVO VARCHAR (1) Default 'S',
ORIGEM VARCHAR (40) NOT NULL,
BAIRRO VARCHAR (25),
CONTATO VARCHAR (40),
OBS BLOB sub_type 1 segment size 80,
RAMAL VARCHAR (10),
TIPOLOG VARCHAR (15),
HOMEPAGE VARCHAR (60),
ULTIMAALTERACAO DATE,
EXCLUIR VARCHAR (1) Default 'N',
ID_OPERADOR INTEGER default 0 NOT NULL,
MOEDA FLOAT,
MEMO BLOB sub_type 1 segment size 80,
SIM_NAO BOOLEAN,
DATA DATE,
NUM INTEGER,
MARCAAUTO VARCHAR (20),
ESTCIVIL VARCHAR (30),
LARGE VARCHAR (50),
DATA_CADASTRO DATE);



/* Primary keys definition */

ALTER TABLE CLIENTE ADD CONSTRAINT PK_CLIENTE PRIMARY KEY (ID_CLIENTE);


/* Indices definition */

CREATE INDEX CLIENTE21 ON CLIENTE (ID_CLIENTE, NOME);
CREATE INDEX CLIENTE22 ON CLIENTE (ATIVO, ID_CLIENTE, TELEFONE, NOME);
CREATE INDEX I_NOME ON CLIENTE (NOME);
CREATE INDEX XWA_SYS_ATIVO_7DCDAAA2 ON CLIENTE (ATIVO);
CREATE INDEX XWA_SYS_CIDADE_7DCDAAA2 ON CLIENTE (CIDADE);
CREATE INDEX XWA_SYS_TELEFONE_7DCDAAA2 ON CLIENTE (TELEFONE);
CREATE INDEX XWA_SYS_ULTIMAALTERACAO_7DCDAAA ON CLIENTE (ULTIMAALTERACAO);



CREATE TABLE CLIENTE_MAILING (
ID_MAILING INTEGER NOT NULL,
ID_CLIENTE INTEGER NOT NULL);



/* Primary keys definition */

ALTER TABLE CLIENTE_MAILING ADD CONSTRAINT PK_CLIENTE_MAILING PRIMARY KEY
(ID_MAILING, ID_CLIENTE);


/* Foreign keys definition */

ALTER TABLE CLIENTE_MAILING ADD CONSTRAINT FK_CLI_CLIENTE FOREIGN KEY
(ID_CLIENTE) REFERENCES CLIENTE (ID_CLIENTE);
ALTER TABLE CLIENTE_MAILING ADD CONSTRAINT FK_CLI_MAILING FOREIGN KEY
(ID_MAILING) REFERENCES MAILING (ID_MAILING);

**** Analisys ****

Query Time
------------------------------------------------
Prepare : 0
Execute : 421.376
Avg fetch time: 15.606,52 ms

Memory
------------------------------------------------
Current: 1,036493E7
Max : 1,049302E7
Buffers: 2.048

Operations
------------------------------------------------
Read : 487.873
Writes : 18
Fetches: 1,401794E7

Plan:
------------------------------------------------
PLAN JOIN (CLIENTE ORDER I_NOME,CLIENTE_MAILING INDEX
(RDB$FOREIGN28,RDB$PRIMARY5,RDB$FOREIGN29,RDB$PRIMARY5,RDB$FOREIGN29))

Enchanced Info:
+--------------------------+-------+-----------+---------+---------+--------
--+
| Table Name | Index | Non-Index |
Updated | Deleted | Inserted |
| | reads |
reads | | | |
+--------------------------+-------+-----------+---------+---------+--------
--+
| RDB$FIELDS| 59 | 0 |
0 | 0 | 0 |
| RDB$RELATION_FIELDS| 59 | 0 | 0 |
0 | 0 |
| RDB$RELATIONS| 4 | 0 | 0
| 0 | 0 |
| RDB$FORMATS| 1 | 0 | 0
| 0 | 0 |
| RDB$RELATION_CONSTRAINTS| 0 | 280 | 0 | 0 |
0 |
| RDB$INDEX_SEGMENTS| 1 | 0 | 0 |
0 | 0 |
| CLIENTE| 537.4 | 0 |
0 | 0 | 0 |
| CLIENTE_MAILING| 843 | 0 | 0 |
0 | 0 |
+--------------------------+-------+-----------+---------+---------+--------
--+

I Hope this could help.

Thanks



""las19682000"" <las_@...> escreveu na mensagem
news:aipc36+pu6c@......

> 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
> Thanks
>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>