Sudheer

If you are dealing with books, you can only use the LIKE operator unless you
force the operator to enter the beginning words of the book.
I have a book application using firebird and doing searches on the titles,
authors, etc... with about 900,000+ references and about 50 simultaneous
users. the LIKE operator killed the server.

You must do some work in you client application. I suggest you do the
following:
1) evaluate the maximum number of words in your title
2) create additional indexed fields in your books table, i.e. k1
VARCHAR(20), k2 VARCHAR(20)... in this case, the words can be 20 char long
3) when you register a new book, parse the words in the title and store them
in the k1...kn keyword fields

When the user does a search, build your query dynamically, i.e.
If the user enters 'Helen Borrie Firebird' (excellent Firebird reference
book!) build the query:

Select * from book b
Where
(
(k1 starting with 'HELEN') or (k2 starting with 'HELEN').... or (kn
starting with 'HELEN')
AND
(k1 starting with 'BORRIE') or (k2 starting with 'BORRIE').... or (kn
starting with 'BORRIE')
AND
(k1 starting with 'FIREBIRD') or (k2 starting with ' FIREBIRD').... or (kn
starting with ' FIREBIRD')
)

You will get immediate results. This method has the advantage of allowing
the words to be partially matched with the STARTING WITH operator. It also
allows to you place the search words in any order.

I also have a 6,000,000 records database including summaries on which I have
to conduct searched. I was forced to use MySQL with the textsearch
capabilities (forget the rest of the features, doesn't get close to
Firebird!)

Hope that helps.

Salim

> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-
> support@yahoogroups.com] On Behalf Of Sudheer Palaparambil
> Sent: Friday, April 29, 2005 5:49 PM


> Hi,
>
> Can somebody tell me how to speed up search on a book title column
> (varchar(100)), the user will be usually entering first 2 or 3 words of
> the
> title. What I am using now is LIKE, but this is very slow on a table which
> contains 120000+ records.
>
> Any suggestion are greatly appreciated.
>
> Thank you.
>
> Sudheer
>