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. 1 1327
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%'); This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: George |
last post by:
Hi,
Anyone has the background for explaining?
I have made a search on my name and I have got a link to another
search engine.
The link's title was the search phrase for the other search engine...
|
by: sinister |
last post by:
I wanted to spiff up my overly spartan homepage, and started using some CSS
templates I found on a couple of weblogs. It looks fine in my browser (IE
6.0), but it doesn't print right. I tested...
|
by: Robert Oschler |
last post by:
I read a while back that MySQL will only use one index per query. (If this
is not so, please tell me and point me to a doc that gives a good
explanation of MySQL's current index usage policy). ...
|
by: Roger |
last post by:
Has anyone done this yet? I know it is easily done in Paradox, but I would
like to do it in VB.Net.
Can anyone point me in the right direction?
Thanks,
Rog
|
by: George Sakkis |
last post by:
After a brief search, I didn't find any python package related to OLAP
and pivot tables. Did I miss anything ? To be more precise, I'm not so
interested in a full-blown OLAP server with an RDBMS...
|
by: jfarthing |
last post by:
I am trying to write a query that joins 3 tables and having a
problem.... First, some structure info, the 3 tables are.....
Address
Name
Street
City
State
Zipcode
...
|
by: Richard |
last post by:
Hi folks, thanks for taking the time to read this (and hopefully point
our where I'm going wrong).
The scenario:
I have a local Access2007 database which links in several read only
mySql...
|
by: Supermansteel |
last post by:
I am joining these 2 tables together in Access 2003 and can't figure out the exact way of writing this script......Can anyone help?
I have the following SQL:
SELECT...
|
by: omerbutt |
last post by:
hi there,
i am making a community site where i am making the member search area, the information from the table is coming from 2 tables already rage_members and rage_members_profile, and i am using...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |