Mysql fulltext search doesn't find, if searched string is not separated with spaces

I'm building a small search with PHP. Here's an example query:

SELECT * FROM tools
WHERE name LIKE "%example%"
ORDER BY name;

A couple of names:

this example will be f开发者_运维问答ound
this example1 will not be found

It only finds the rows, if the string that is being searched is a separate word. Any advice? :)

Thanks, Martti Laine

I ran the following script

CREATE TABLE tools ( 
  name TEXT, 
  FULLTEXT (name) 
) ENGINE=MyISAM;

INSERT tools VALUES('this example will be found');
INSERT tools VALUES('this example1 will not be found');

SELECT * FROM tools
WHERE name LIKE "%example%"
ORDER BY name;

Which gave the output

this example will be found 
this example1 will not be found

I even got the same output when running the SELECT without the FULLTEXT index.

Can you post the DDL for the creation of your database and any errors you receive? Also, does the SQL execute correctly in MySQL Workbench or have you only tried it from your PHP code?

(I realise this is not strictly an answer to your question but posting as an answer allows for better formatting of the text)

EDIT: After seeing the comment from ajreal I also tried

SELECT * FROM tools
WHERE MATCH(name) AGAINST ('example')

and this time it did not return any rows...

That's the way it's supposed to work. MySQL's Full Text Search using what's known as stemming to build the search index. So it'll take a word like shopping and store shop (since it's the same word). That way, when you search for one variant of the word, it can find all variants of the word.

However, in your case, that may not be the greatest situation. Well, for that, you have 2 possibilities:

1. Use LIKE with Full Text searching.

   Well, LIKE performs a direct case-insensitive search. Full Text Search performs a stemmed word search. Both have significant flaws. But together they will let you hit a broader scope of results.

2. Use a 3pd search engine tool such as Sphinx or SOLR.

   This will likely be the most efficient option, since these tools are both very good in how they search, and are very fast. Basically, the tools indexes the MySQL database from time to time (depending on the setup), and then you search against the tool instead of MySQL. This is the most robust option, and the most flexible.