The problem is that "like" cannot use an index, so everything becomes a
table space scan.

I don't know how this will work - It is similar to a couple of
suggestions I have already seen in response to your question.

1. make the primary key of your book table something other than the
title. ISBN is a good choice. I'll just call this <<primary key>>

2. create a separate keyword to book relationship table, that is
structured as keyword, <<primary key>>. I would be tempted to make its
primary key both of its columns.

3. select b.title, <<primary key>>, count(*)
from kwd_book_rel a,
books b
where a.keyword in (.....)
and b.<<primary key>> = a.<<primary key>>
group by b.title
order by 2 desc

will return a list of all titles containing the keywords, in order of
most to least hits.

The list can be pared down more with a little effort, but that's the
gist.

On Fri, 2005-04-29 at 20:19 +0530, Sudheer Palaparambil wrote:
> 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
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>