Wenn man die Vorschlage aus Herrn Gödde folgen wird, man kann ein "Googler" bauen..... d.h., wenn man "insert" oder "update" macht, dann eine Tabelle mit Suchwörte und Index wird aktualiziert...... stimmt das ?
 
This will need a complete change in the conception - this is really the only way to get speed in this kind of search -, but must be considered how frequently this kind of search will be needed and the words need to be "dismounted" at the insert/update time. And each gotten word will need to be converted in a record in this search table. This can mean that the accessories operations will increase the time to insert/update operations. And what will be gained as result, may mean a general loss. So, this solutions need to be clearly considered, because the positive results may not be great enough to justify its implementation, may be better stay with the current search results.
 
Think about.
 
Roberto Camargo,
Rio de Janeiro/BR


________________________________
From: Frank Schlottmann-Gödde <frank@...>
To: firebird-support@yahoogroups.com
Sent: Thursday, July 12, 2012 12:43 PM
Subject: Re: [firebird-support] Slow query with like '%xxx%' clause

On 07/12/2012 03:17 PM, peixinhosdalua wrote:

> 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%?


You may try to create an extra search-table sth. like
(searchword,tablename,fieldname,ID) and fill this on insert,update or
delete using triggers on your original tables,
Create one or more  stored procedures that do a
for select tablename,id from searchtable where searchword like
'%whatever%'  into ..
(maybe trying a starting with first)

and returns the values you want (via execute statement)  .

This will reduce the table scan to one table.

hth
fsg

--
"Fascinating creatures, phoenixes, they can carry immensely heavy loads,
  their tears have healing powers and they make highly faithful pets."
      - J.K. Rowling




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

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

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]