I have a table that contains 3 text fields, and an ID one.
The table exists solely to get collection of ID's of posts based on relevance of a user search.
Problem is I lack the Einsteinian intellect necessary to warp the SQL continuum to get the desired results -
SELECT `id` FROM `wp_ss_images` WHERE `keywords` LIKE '%cute%' OR `title` LIKE '%cute%' OR `content` LIKE '%cute%'
- Is this really enough to get a relevant-to-least-relevant list, or is there a better way?
Minding of course databases could be up to 20k rows, I want to keep it efficient.
Here is an update - I've gone the fulltext route -
SELECT `id` FROM `wp_ss_images` WHERE MATCH (`keywords`,`title`,`content`) AGAINST ('+cute +dog' IN BOOLEAN MODE);
However it seems to be just grabbing all entries with any of the words. How can I refine this to show relevance by occurances?