470,644 Members | 1,235 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,644 developers. It's quick & easy.

joining two tables for a search engine

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 t.name from titles t, words w, index i where (w.name in
('harry", "potter"))
and (w.word_id = i.id) and (i.title_id = t.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.

Apr 23 '06 #1
1 1235
none wrote:
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 t.name from titles t, words w, index i where (w.name in
('harry", "potter"))
and (w.word_id = i.id) and (i.title_id = t.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.


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 c.name from (
select t.name from titles t, words w, index i where (w.name in
('harry', 'potter'))
and (w.word_id = i.id) and (i.title_id = t.id)
) c where strtoupper(t.name) like strtoupper('%harry%potter%');


Apr 24 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by George | last post: by
81 posts views Thread by sinister | last post: by
1 post views Thread by Robert Oschler | last post: by
3 posts views Thread by George Sakkis | last post: by
omerbutt
4 posts views Thread by omerbutt | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.