2

I understand LIKE results with wildcards etc. What I need to know is a good way to get search results with the most relative at the top.

For Example: I search for "Front Brake CarModel" or something similar.

Currently I explode the string by spaces and create an addition OR/WHERE state so the query would look something like this . 

SELECT * FROM table WHERE article_text LIKE '%Front%' OR article_text LIKE '%Brake%' OR article_text LIKE '%CarModel%'

Due to my novice searching skills, this is not great as it get results for every word in the search term. What I would like to happen is get the result and sort with the articles with the most found words at the top. If that makes sense.

Advice?

EDIT : Table is type InnoDB and cannot change type due to foreign key restraints. Thus removing the ability for me to use FULLTEXT indexing :(

Tyler
  • 668
  • 1
  • 8
  • 22
  • possible duplicate of [Is Full Text search the answer?](http://stackoverflow.com/questions/614358/is-full-text-search-the-answer) – Marcus Adams Mar 20 '12 at 17:46
  • @MarcusAdams Unfortunately not. My table is InnoDB and cannot change the type. So I cannot use FULLTEXT indexing. – Tyler Mar 20 '12 at 18:04
  • So why not just create a new table with just the search text, and a key into the "real" table as MyIASM? – Tyler Eaves Mar 20 '12 at 18:07

1 Answers1

1

This can be done easily with a fulltext index.

ALTER TABLE table ADD FULLTEXT INDEX `ft_search` (`article_text`);

SELECT *, MATCH(article_text) AGAINST('Front Brake CarModel') AS score
FROM table
WHERE MATCH(article_text) AGAINST('Front Brake CarModel') ORDER BY score DESC;
kba
  • 19,333
  • 5
  • 62
  • 89
Rob
  • 12,659
  • 4
  • 39
  • 56
  • This looks promising however it appears the table I am using doesn't support FULLTEXT indexing. "The used table type doesn't support FULLTEXT indexes" – Tyler Mar 20 '12 at 17:55
  • You are not using the MyISAM engine. Most likely your table is InnoDB. Use `ALTER TABLE table ENGINE=MyISAM` to change engine and try again. – kba Mar 20 '12 at 18:05
  • If you are using innodb tables then rather than changing the table to myisam, I usually move only the columns I need for the fulltext index to a separate myisam table. – Rob Mar 20 '12 at 18:18