Improving '% LIKE %' Query on Firebird Database

Question

Situation:

I am trying to write a efficient query using "LIKE" statement to look after a piece of text into a column with short texts.

Model:

Table 'EVENTSGENERAL' : { ID (KEY), GENERATOR_ (FK), DATETIME, COMPUTERNAME, OSLOGIN, DBLOGIN, INFOTYPE, INFO }

Table 'EVENTSGENERATORS' : { ID (KEY), GENERATOR_ (FK), SHORTNAME, LONGNAME }

Table 'EVENTSINFOTYPES'  : { ID (KEY), GENERATOR_ (FK), VERSION_, INFOTYPE, DESCRIPTION }

Indexes : EVENTSGENERAL.GENERATOR, EVENTSGENERAL.DATETIME, EVENTSINFOTYPES.INFOTYPE All Ascending.

My Query:

SELECT FIRST @first SKIP @skip A.ID,B.LONGNAME,  A.DATETIME, A.COMPUTERNAME,A.OSLOGIN, A.DBLOGIN, C.DESCRIPTION, A.INFO
FROM EVENTSGENERAL A JOIN EVENTSGENERATORS B ON B.GENERATOR_ = A.GENERATOR_
JOIN EVENTSINFOTYPES C ON C.GENERATOR_ = A.GENERATOR_ AND C.INFOTYPE = A.INFOTYPE
WHERE C.DESCRIPTION LIKE '%VALUE%'

Problem:

This query will run on extra large databases. Is there any way i can improve it ? I am using a Firebird database.

Thanks in advance.

Answer 1

No, unfortunately not, because you use wildcards (%) on both sides of the LIKE keyword. The leading (beginning) wildcard means that no index can be used to help improve the search speed, and therefore every row has to be examined to see if it meets the criteria.

You can speed things up somewhat by changing to LIKE 'VALUE%' instead; an index can at least be used to limit the rows being searched to those starting with VALUE.

Answer 2

Full text search (using %VALUE%) is slow, because there is no efficient way to do that. It's like grabbing a dictionary to make a list of all words that contain the letter Q. That takes forever. On the other hand, if you just look for VALUE% it's a lot easier (if the search field is indexed). It's like making a list of all words that start with the letter Q. That's easy if the words are alphabetically sorted (like they are in a dictionary). You can easily find the first and last words, and you can be sure that you need everything in between and nothing else.

Some databases allow a full text search, they can index each word in a column, but only the full word. So searching for %q% still would be slow, and searching for q% would be faster. I'm not sure whether or not Firebird supports it.

If you have a large database and you want to be able to search for %q%, you should look into other, specialized solutions, like Lucene or Xapian