joining two tables for a search engine

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I am trying to optimize a search engine.

let's say I have 3 tables. one has titles, one has words, and an index
where each title is broken into words (title_id and word_id)

I want to be able to search terms in any order, so "potter harry" is
the same as "harry potter".

select from titles t, words w, index i where ( in
('harry", "potter"))
  and (w.word_id = and (i.title_id =;

This produces a list of titles matching my search times. however, with
over 3 million products, it can be quite slow. 3 seconds is too slow.
sometimes it takes a minute.

is there a better way to do a join when there are more search terms
like "harry potter and the chamber of secrets"?

the fastest way I found was to get the word count for each term, join
on the least used word, and then make sure the remaining terms are in
the titles. but sometimes it returns 10,000+ titles.

what is the best way to do this?

I searched for information on join types but it is way too confusing
for me and does not explain it in a way I can understand.

I am willing to pay via paypal $20 if someone can explain it to me over
the phone in detail.

Re: joining two tables for a search engine

none wrote:
Quoted text here. Click to load it

what indexes do you have on the 3 tables?  any?

make sure the

create index words_I on words (words,word_id)

hopefully the ids are indexed on the other 2 tables as well.

you could also restructure the query to get:

select from (
select from titles t, words w, index i where ( in
('harry', 'potter'))
  and (w.word_id = and (i.title_id =
) c where strtoupper( like  strtoupper('%harry%potter%');

Site Timeline