make joins quicker

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
prima-nocte
Forum Newbie
Posts: 9
Joined: Tue Oct 04, 2005 7:28 am

make joins quicker

Post by prima-nocte »

Hi

I'm making a search engine and i am using joins for the search.
'search_keywords' are the keywords the engine is looking through.
'search_keyword_reference' is a table with an urlID and a keyID to bind the keywords with the urls in the database.

this is the query if i search on the keywords "foo" "bar" "test"

Code: Select all

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: Select all

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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Use a FULLTEXT index. It's a built in index feature that allows MySQL to be its own search engine. I use it all over and its quite quick.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
prima-nocte
Forum Newbie
Posts: 9
Joined: Tue Oct 04, 2005 7:28 am

Post by prima-nocte »

Thanks. I tried that option but it's quite slower than this method because there are a lot of entries.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Have you walked through all the optimization tips from MySQL?

http://dev.mysql.com/doc/refman/5.0/en/query-speed.html
prima-nocte
Forum Newbie
Posts: 9
Joined: Tue Oct 04, 2005 7:28 am

Post by prima-nocte »

yes i did that :) thanks

I found another way around it. The JOINS are just very slow. I now get the results from the database with separate queries for each keyword.. push them in different arrays for each keyword then with array_intersect combine them and walk through them that way. about 60 times quicker :) it shouldn't be but it is.. go figure :)

but thanks anyway :)
Post Reply