Best match using mysql like statement(natural match)

Question

Hello i want to search table and get best match from the table using like not FULL TEXT SERCH.iS there any wat to do?

Answer 1

Is this what you're looking for?

SELECT username FROM users WHERE username LIKE '%value%';

Answer 2

If you're asking for full-text search with results ordered by relevance then yes there are solutions.. but it's not trivial. These are the more 'enterprise' level solutions:

Solr - http://lucene.apache.org/solr/

Solr is the popular, blazing fast open source enterprise search platform from the Apache Lucene project. Its major features include powerful full-text search, hit highlighting, faceted search, dynamic clustering, database integration, and rich document (e.g., Word, PDF) handling. Solr is highly scalable, providing distributed search and index replication, and it powers the search and navigation features of many of the world's largest internet sites.

Sphinx - http://sphinxsearch.com/

Generally, it's a standalone search engine, meant to provide fast, size-efficient and relevant fulltext search functions to other applications. Sphinx was specially designed to integrate well with SQL databases and scripting languages. Currently built-in data sources support fetching data either via direct connection to MySQL or PostgreSQL, or using XML pipe mechanism (a pipe to indexer in special XML-based format which Sphinx recognizes).

It's impossible to help narrow down the correct solution without knowing more about your systems and limitations.

Answer 3

do it in steps:

a. first look for an exact match (SELECT * FROM business WHERE name = 'value')

if this fails then:

b. split the search string into words, run the query for each word (add results for each word to an array where the key is the row id and the value is the relevance, for each word found increase the relevance by 1, then you can simply sort your array (ie most words matched = highest relevance)

b is very inneficient and really you should be using full text (either mysql built in full text or sphinx/lucene etc as mentioned above)