How to improve text accuracy in mysql?

Question

I am running a simple mysql text query that searches for users on my site based on their "display name". Example request below - in this example we are looking for a "Lancaster toy store":

SELECT MATCH(`display_name`) AGAINST ('lancaster toy store') as `rel`
WHERE MATCH(`display_name`) AGAINST ('lancaster toy store')
ORDER BY `rel` DESC

It works well in that it is gaining good results, but an example of the results would be:

• toy store Charlotte
• toy store on the Broadway
• toy stores in Arizona.
• lancaster toy store
• toys on the east coast

As you can see, my problem is that people are looking for a “Lancaster toy store,” and the obvious best result is near the middle or bottom.

I also use the porter-stem technique.

Any ideas on how to get more accurate results?

UPDATE

( - " " ):

SELECT `id`,
       MATCH (`display_name`) AGAINST ('lancast* restor*' IN BOOLEAN MODE)
           AS `RELEVANCY`
FROM `users`
WHERE `status` = 'active'
&& MATCH (`display_name`) AGAINST ('lancast* restor*' IN BOOLEAN MODE)
ORDER BY `RELEVANCY` DESC
LIMIT 25

:

• -
• ReStore 15 NH
• Morris Habitat ReStore
• Habitat ReStore Lima Ohio
• Charlotte ReStore
• ReStore Montgomery County
• ReStore
• ReStore
• ReStore

Answer 1

Erm, , ( + ), :

MATCH (display_name) 
AGAINST ('lancast* restor* >lancaster >restore' IN BOOLEAN MODE)

, ,

ORDER BY MATCH (display_name) 
AGAINST ('lancast* restor* >lancaster >restore' IN BOOLEAN MODE) DESC

Answer 2

MySQL , , - . Lucene, API (.NET, PHP Zend_Search_Lucene ..), , .

, , . , , mysql WILL , , Lucene.

! , , , , .

,

Answer 3

, , MySQL, , , # 4:

4. toy store of lancaster

, FROM, , , . - ? , BOOLEAN MODE ? BOOLEAN MODE, , №4 , .

?

Answer 4

: " , ?" - : - , MySQL, . Sphinx (http://sphinxsearch.com/), , , MySQL .

Answer 5

. , Solr - , , .

Answer 6

If it gets the correct result and all you really need is the best order, you can re-sort the results. As a first option, get the word frequency distribution for your body and the ranking results with a rarer word match.

Throw the word order in as well. OTOH general search is the same art as science.