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"
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:
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. So this isn't an option.
As i said the first option works but is quite slow and gets slower the more keywords are entered in the search field.
Has anybody any idea how i can optimize the first code with less joins and make it a bit quicker? thanks