Caro "peixinhosdalua",
 
There is no way to increase such kind of searches in a database, because the searches are made line per line, searching inside each line by the occurrence of your needed search by displacement of the searching word, char by char.....
 
You are right that such kind of search has to be made this way, but, then, there is no way to get better results (in time). They will never be so fast as using wildcards only at the right side of the searched word, because this search is made only in the first part ( = at the first char plus the length ) and uses the index for this search. In your case, still if the word search is made using the index table, this will be so slowly as if made at the data table itself. And if the search is composed by a OR clause, this search is made twice, with double delay, and so on....
 
For this kind of search, there is no tip and no trick to increase the speed.
 
Good luck, best regards,
Roberto Camargo,
Rio de Janeiro/Brasil.


________________________________
From: peixinhosdalua <peixinhosdalua@...>
To: firebird-support@yahoogroups.com
Sent: Thursday, July 12, 2012 10:17 AM
Subject: [firebird-support] Slow query with like '%xxx%' clause

Hello,

I made a few query to count the time. I tried with the CLIENTES having 3553 records and with 1000 records. The changes in time performance is significant! Note that now i am not testing with the VIEW (so not having any joined tabled).

30 SEG (3553 records) 9 SEG (1000 records) (with the VIEW)

select first 20 * from LISTA_CLIENTES where (CLIENTE like '%crist%'  or MORADA like '%crist%' or LOCALIDADE like '%crist%' or CLIENTE like '%crist%' or PAIS like '%crist%' or PAIS_NOME like '%crist%' or  TELEFONE like '%crist%' or EMAIL like '%crist%' or CONTRIBUINTE like '%crist%' or COND_PAGAMENTO_DESCRICAO like '%crist%') order by CLIENTE


20 SEG (3553 records) 6 SEG (1000 records) (without the VIEW)

select first 20 * from CLIENTES where CLIENTE like '%crist%'
or MORADA like '%crist%' or LOCALIDADE like '%crist%' or CLIENTE like
'%crist%' or PAIS like '%crist%' or TELEFONE like '%crist%' or EMAIL like '%crist%' or CONTRIBUINTE like
'%crist%' order by CLIENTE

total records in CLIENTES = 3553

25 SEG (3553 records) 8 SEG (1000 records) (without the VIEW)
select first 20 * from CLIENTES where CLIENTE like '%crist%'
or MORADA like '%crist%' or LOCALIDADE like '%crist%' or CLIENTE like
'%crist%' or PAIS like '%crist%' or TELEFONE like '%crist%' or EMAIL like '%crist%' or CONTRIBUINTE like
'%crist%'


15 SEG (3553 records) 8 SEG (1000 records) (without the VIEW)
select first 20 * from CLIENTES where CLIENTE like '%crist%'
or MORADA like '%crist%' or LOCALIDADE like '%crist%' or CLIENTE like
'%crist%' or PAIS like '%crist%' or TELEFONE like '%crist%'


4 SEG (3553 records) 2 SEG (1000 records) (without the VIEW)
select first 20 * from CLIENTES where CLIENTE like '%crist%'
or MORADA like '%crist%'

Regarding your suggestions, i need to make a search by '%word%' not 'word%'. For example, most companies have a commercial name or brand that they use and these are different from the juridic name. It is the juridic name that is inserted in the table because this table is using to make invoices. So it is standard to have companies names like: Something Brand LTD and people search for Brand and if they do it like 'Brand%' never appears.

Same can happen for phones if you search with out without prefixes. Also the VAT-ID. For example VAT's in Europe have letters at beginning but most people omitted the letters when searching for VAT-ID from their own country.

This is even more true when searching for products.

So, how can i improve the usage of the like or containing with the %word%?
Thanks.



------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org/ and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links



[Non-text portions of this message have been removed]