QUESTION: Interesting, why do you have ORDER BY in the view definition? I view views as 'virtual tables', and tables have no inherent ordering. ANSWER: because i read in FB webpage that moving the order by to inside the VIEW or procedures it optimizes and speeds up things.
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). QUESTION: What are the plans of these two different queries? Is the difference in execution time equally visible if you query something that have less than 20 matches in total (e.g. 'where CLIENTE like '%wxtwaq%')? 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). Field #01: LISTA_CLIENTES.COND_PAGAMENTO_DESCRICAO Alias:COND_PAGAMENTO_DESCRICAO Type:STRING(45) Field #02: LISTA_CLIENTES.PAIS_NOME Alias:PAIS_NOME Type:STRING(60) Field #03: LISTA_CLIENTES.IDCLIENTES Alias:IDCLIENTES Type:INTEGER Field #04: LISTA_CLIENTES.CLIENTE Alias:CLIENTE Type:STRING(100) Field #05: LISTA_CLIENTES.MORADA Alias:MORADA Type:STRING(100) Field #06: LISTA_CLIENTES.CP Alias:CP Type:STRING(10) Field #07: LISTA_CLIENTES.LOCALIDADE Alias:LOCALIDADE Type:STRING(50) Field #08: LISTA_CLIENTES.PAIS Alias:PAIS Type:STRING(2) Field #09: LISTA_CLIENTES.TELEFONE Alias:TELEFONE Type:STRING(15) Field #10: LISTA_CLIENTES.FAX Alias:FAX Type:STRING(15) Field #11: LISTA_CLIENTES.EMAIL Alias:EMAIL Type:STRING(100) Field #12: LISTA_CLIENTES.CONDICOES_PAGAMENTO Alias:CONDICOES_PAGAMENTO Type:SMALLINT Field #13: LISTA_CLIENTES.REGIME_IVA Alias:REGIME_IVA Type:STRING(1) Field #14: LISTA_CLIENTES.PER_DESCONTO Alias:PER_DESCONTO Type:NUMERIC(9,2) Field #15: LISTA_CLIENTES.NUNCA_BLOQUEAR Alias:NUNCA_BLOQUEAR Type:STRING(1) Field #16: LISTA_CLIENTES.CONTRIBUINTE Alias:CONTRIBUINTE Type:STRING(25) Field #17: LISTA_CLIENTES.COD_CLIENTE_FAT Alias:COD_CLIENTE_FAT Type:STRING(15) Field #18: LISTA_CLIENTES.ATIVO Alias:ATIVO Type:STRING(1) Field #19: LISTA_CLIENTES.VALIDADO Alias:VALIDADO Type:STRING(1) PLAN SORT (SORT (JOIN (LISTA_CLIENTES CP NATURAL, LISTA_CLIENTES C INDEX (IDX_CLIENTES3), LISTA_CLIENTES P INDEX (IDX_PAISES1)))) Executing statement... 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. #####################################################################3 THIS returns more than 20 records (the part of word in the like is a very common in Portuguese language) and is faster than the previous example! Preparing statement: select first 20 * from LISTA_CLIENTES where (CLIENTE like '%ão%' or MORADA like '%ão%' or LOCALIDADE like '%ão%' or CLIENTE like '%ão%' or PAIS like '%ão%' or PAIS_NOME like '%ão%' or TELEFONE like '%ão%' or EMAIL like '%ão%' or CONTRIBUINTE like '%ão%' or COND_PAGAMENTO_DESCRICAO like '%ão%') order by CLIENTE Statement prepared (elapsed time: 0.000s). Field #01: LISTA_CLIENTES.COND_PAGAMENTO_DESCRICAO Alias:COND_PAGAMENTO_DESCRICAO Type:STRING(45) Field #02: LISTA_CLIENTES.PAIS_NOME Alias:PAIS_NOME Type:STRING(60) Field #03: LISTA_CLIENTES.IDCLIENTES Alias:IDCLIENTES Type:INTEGER Field #04: LISTA_CLIENTES.CLIENTE Alias:CLIENTE Type:STRING(100) Field #05: LISTA_CLIENTES.MORADA Alias:MORADA Type:STRING(100) Field #06: LISTA_CLIENTES.CP Alias:CP Type:STRING(10) Field #07: LISTA_CLIENTES.LOCALIDADE Alias:LOCALIDADE Type:STRING(50) Field #08: LISTA_CLIENTES.PAIS Alias:PAIS Type:STRING(2) Field #09: LISTA_CLIENTES.TELEFONE Alias:TELEFONE Type:STRING(15) Field #10: LISTA_CLIENTES.FAX Alias:FAX Type:STRING(15) Field #11: LISTA_CLIENTES.EMAIL Alias:EMAIL Type:STRING(100) Field #12: LISTA_CLIENTES.CONDICOES_PAGAMENTO Alias:CONDICOES_PAGAMENTO Type:SMALLINT Field #13: LISTA_CLIENTES.REGIME_IVA Alias:REGIME_IVA Type:STRING(1) Field #14: LISTA_CLIENTES.PER_DESCONTO Alias:PER_DESCONTO Type:NUMERIC(9,2) Field #15: LISTA_CLIENTES.NUNCA_BLOQUEAR Alias:NUNCA_BLOQUEAR Type:STRING(1) Field #16: LISTA_CLIENTES.CONTRIBUINTE Alias:CONTRIBUINTE Type:STRING(25) Field #17: LISTA_CLIENTES.COD_CLIENTE_FAT Alias:COD_CLIENTE_FAT Type:STRING(15) Field #18: LISTA_CLIENTES.ATIVO Alias:ATIVO Type:STRING(1) Field #19: LISTA_CLIENTES.VALIDADO Alias:VALIDADO Type:STRING(1) PLAN SORT (SORT (JOIN (LISTA_CLIENTES CP NATURAL, LISTA_CLIENTES C INDEX (IDX_CLIENTES3), LISTA_CLIENTES P INDEX (IDX_PAISES1)))) Executing statement... 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: -44 bytes. Total execution time: 6.437s Script execution finished. ##################333 The tables PAISES has: CREATE INDEX IDX_PAISES1 ON PAISES (CODIGO_ISO_3166_1); I forgot to paste it. QUESTION: how many different values are there for PAISES.CODIGO_ISO_3166_1? ANSWER: select count(*) from paises =247 QUESTION: Also, when you say that as you add more fields to the WHERE clause the slower it gets, are there any fields in particular that slows things down? Is it the same if you add a field from CLIENTES or one of the other tables and does it matter what size the field is? ANSWER: I do not see and logic in choosing the fields. Adding or removing fields appears to change performance but i can't finger it out any difference depending on the fields types of sizes.
