>QUESTION: How many records do each table contain and what is the result of
>
> select count(*)
> FROM CLIENTES c
> inner join PAISES p on p.CODIGO_ISO_3166_1=c.PAIS
> inner join COND_PAGAMENTO cp on
> cp.IDCOND_PAGAMENTO=c.CONDICOES_PAGAMENTO
>
>ANSWER: the result is 3553 or 1000 (with my seconds tests with less records) and these numbers are equal to the
>select (*) from CLIENTES (or the VIEW).
>
>ANSWER: this returns only 5 records (i am using now only 1000 records in the table):
>
>Starting transaction...
>Preparing statement: select first 20 * from LISTA_CLIENTES where (CLIENTE like '%rrão%' or MORADA
> like '%rrão%' or LOCALIDADE like '%rrão%' or
> CLIENTE like '%rrão%' or PAIS like '%rrão%' or PAIS_NOME like '%rrão%' or
> TELEFONE like '%rrão%' or EMAIL like '%rrão%'
> or CONTRIBUINTE like '%rrão%' or COND_PAGAMENTO_DESCRICAO like '%rrão%')
> order by CLIENTE
>Statement prepared (elapsed time: 0.000s).
>Statement executed (elapsed time: 0.000s).
>6049 fetches, 0 marks, 0 reads, 0 writes.
>0 inserts, 0 updates, 0 deletes, 2006 index, 5 seq.
>Delta memory: 52548 bytes.
>Total execution time: 7.515s
>Script execution finished.

Wonder if it's 'statement executed' and not 'total execution time' that says anything about the time the query takes to execute? If not, the execution time is ridiculous. For testing, I tried CONTAINING on a couple of fields on a table containing 1.2 million records (using a non-existing value, so 0 rows returned), and the execution time was 12 seconds (4-5 seconds on subsequent executions). Then I joined to another table containing 1.5 million records (theoretically the join is 1:M, in practice mostly 1:1 or 1:2, indexed of course). This other table had no field bigger than VARCHAR(16), but when I did CONTAINING on that fields also, the execution time increased to about 28 seconds.

Admittedly, I didn't use a view. Still, I'm very surprised that a simple query like yours on tiny tables could take one minute to execute.

Set