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.idThis 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.idIt 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