mysql - Good algorithm for searching DB for a given string -

mysql - Good algorithm for searching DB for a given string -

i'm working on web app (php + mysql) user can search other users inputting search string.

i need match user's input string 2 columns (username , fullname) of 'user' table in db , homecoming relevant (20 or 50) matches. optimally, need take consideration misspellings.

how can approach this? i'm not looking reinvent wheel here.

you may using mysql total text search:

please have @ this, this, this articles.

i want explain boolean total text search; advise please go through full text search using query expansion also.

let's @ illustration table given on dev.mysql.com:

mysql> select * articles; +----+-----------------------+------------------------------------------+ | id | title | body | +----+-----------------------+------------------------------------------+ | 1 | postgresql tutorial | dbms stands database ... | | 2 | how utilize mysql | after went through ... | | 3 | optimizing mysql | in tutorial show ... | | 4 | 1001 mysql tricks | 1. never run mysqld root. 2. ... | | 5 | mysql vs. yoursql | in next database comparing ... | | 6 | mysql security | when configured properly, mysql ... | +----+-----------------------+------------------------------------------+ mysql> select * articles match (title,body) against ('"database comparison"' in boolean mode); +----+-------------------+------------------------------------------+ | id | title | body | +----+-------------------+------------------------------------------+ | 5 | mysql vs. yoursql | in next database comparing ... | +----+-------------------+------------------------------------------+

order matters, when words quoted:

mysql> select * articles match (title,body) against ('"comparison database"' in boolean mode); empty set (0.01 sec)

when remove quotes, search rows, containing words "database" or "comparison":

mysql> select * articles match (title,body) against ('database comparison' in boolean mode); +----+---------------------+------------------------------------------+ | id | title | body | +----+---------------------+------------------------------------------+ | 1 | postgresql tutorial | dbms stands database ... | | 5 | mysql vs. yoursql | in next database comparing ... | +----+---------------------+------------------------------------------+

order doesn't matter now:

mysql> select * articles match (title,body) against ('comparison database' in boolean mode); +----+---------------------+------------------------------------------+ | id | title | body | +----+---------------------+------------------------------------------+ | 1 | postgresql tutorial | dbms stands database ... | | 5 | mysql vs. yoursql | in next database comparing ... | +----+---------------------+------------------------------------------+

if want rows, containing either word "postgresql" or phrase "database comparison", should utilize request:

mysql> select * articles match (title,body) against ('postgresql "database comparison"' in boolean mode); +----+---------------------+------------------------------------------+ | id | title | body | +----+---------------------+------------------------------------------+ | 1 | postgresql tutorial | dbms stands database ... | | 5 | mysql vs. yoursql | in next database comparing ... | +----+---------------------+------------------------------------------+

fiddle try

make sure, words, searching for, not in list of stopwords, ignored. (and words 'is','the' stopwords , ignored)

to enhance sorting of results in boolean mode can utilize next queries:

(assuming have total 2 words in user's input string) then.

select column_names, match (text) against ('word1 word2') col1 table1 match (text) against ('+word1 +word2' in boolean mode) order col1 desc;

(if have 3 words in user's input string) then..

select column_names, match (text) against ('word1 word2 word3') col1 table1 match (text) against ('+word1 +word2 +word3' in boolean mode) order col1 desc;

using first match() we score in non-boolean search mode (more distinctive). second match() ensures results want (with 3 words).

mysql database search text