Page 1 of 1

make joins quicker

Posted: Thu Nov 03, 2005 4:28 am
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

Posted: Thu Nov 03, 2005 9:42 am
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.

Posted: Fri Nov 04, 2005 2:58 am
by prima-nocte
Thanks. I tried that option but it's quite slower than this method because there are a lot of entries.

Posted: Fri Nov 04, 2005 7:18 am
by feyd
Have you walked through all the optimization tips from MySQL?

http://dev.mysql.com/doc/refman/5.0/en/query-speed.html

Posted: Fri Nov 04, 2005 7:51 am
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 :)