make joins quicker


Active Topics	New Topics	My Topics	Search	Profile	Inbox	Register	Rules/TOS

Add to hotlist

Mark This Forum Read

Printer Friendly Version

Email this thread to a friend

Featured Web Site Template

Hundreds More at Free Site Templates.com!

Web Site Partners

Sponsored Links

Jet City Software

Whos Here ?

Reflects user activity within the last 5 minutes

Moderator(s): Prowler, jcokos

Forum Index · Search Engine Forums · Building, Hosting and Maintaining Your Website · Coding & Databases - PHP, ASP, Perl, etc. · make joins quicker

Previous Topic Next Topic

Member	Message
primanocte Joined: Nov 03, 2005 # Posts: 1	Posted: 2005-Nov-03 10:12 Hi I'm making a search engine and i am using joins for the search. this is the query if i search on the keywords "foo" "bar" "test" Code: [copy] SELECT s1.id,s1.url,s1.thumbs,s1.kind,s4.indexdate FROM search_urls AS s1 JOIN search_keywords AS s5 ON s5.id = s25.keyID JOIN search_keyword_reference AS s25 ON s25.urlID = s1.id JOIN search_keywords AS s6 ON s6.id = s26.keyID JOIN search_keyword_reference AS s26 ON s26.urlID = s1.id JOIN search_keywords AS s7 ON s7.id = s27.keyID JOIN search_keyword_reference AS s27 ON s27.urlID = s1.id JOIN search_indexed AS s4 ON s1.id = s4.urlID WHERE s1.kind = 'movies' AND ( s5.keyword LIKE '%foo%' AND s6.keyword LIKE '%bar%' AND s7.keyword LIKE '%test%' ) GROUP BY s1.id as you can see i have to join the search_keywords and search_keyword_reference for every keyword i enter. This makes it a bit slow. I have tried this: Code: [copy] SELECT s1.id,s1.url,s1.thumbs,s1.kind,s4.indexdate FROM search_urls AS s1 JOIN search_keyword_reference AS s2 ON s2.urlID = s1.id JOIN search_keywords AS s3 ON s3.id = s2.keyID JOIN search_indexed AS s4 ON s1.id = s4.urlID WHERE s1.kind = 'movies' AND ( s3.keyword LIKE '%foo%' AND s3.keyword LIKE '%bar%' AND s3.keyword LIKE '%test%' ) GROUP BY s1.id where i join the search_keywords and search_keyword_reference only once (as i would expect should be enough) but it doesn't work this way. It appears that he only searches for the first keyword en for the second and third keyword he searches in the first keyword. So "foo" and "bar" doesn't return anything. But "automobile" and "auto" does. Has anybody any idea how i can optimize the first code with less joins and make it a bit quicker? thanks (Copy Shortcut)

Previous Topic Next Topic

Forum Index · Search Engine Forums · Building, Hosting and Maintaining Your Website · Coding & Databases - PHP, ASP, Perl, etc. · make joins quicker

You are not permitted to post messages in this forum or topic, because of one or more of the following reasons:

You have not yet logged in, or registered properly as a member
You are a member, but no longer have posting rights.
This is a private forum, for which you do not have permissions.

If you are a recent member, it's possible that you simply have not yet confirmed your account. Please check your email for a message entitled 'JimWorld Forums: Confirm Your Account' and follow the instructions contained within.

If you cannot find this message, click here to Re-Send it.

If you are still experiencing problem, please read the Login Assistance Article for some advice on what may be causing your login not to work properly.

Switch to Advanced Editor and ... Create a New Topic or Reply to this Thread

New posts

Forum is locked