使用全文而不是LIKE php mysql即时搜索

I want to do an instant search for my website using jquery php and mysql. Previously i used to take the text from .searchbox on keyup and send request to php via ajax.

PHP

$query="Select from names where concat(fname," ",lname) Like '$search%' "

this would work fine. But i heard Fulltext is tons time faster than LIKE. But its happens to work only if someone writes a full name . Therefore fails to autocomplete searches. I wonder whether is there any alternative to my above code which is way much faster using fulltext or any other means.

I suggest

in PHP

$search_name=explode(" ", addslashes($search));
fname =  $search_name[0];
if(!empty($search_name[1])) { 
    $lname = $search_name[1];
    $sql_add = "(lname LIKE '$lname%') OR";
} else $sql_add="";

in sql

"Select from names where $sql_add fname LIKE '$fname%'"

It is true that maybe will have a lot of results, but that is better than some of the results are lost because of sql condition.

If you're looking for a robust search system, then you need to look into Apache Lucene. From what I read in several forums, MySQL FullText is really slow comparing to Apache Lucene. You can also look into Apache Solr (which is based on Apache Lucene).

If you're trying to build an Autocomplete Field, then you need to look into: - Twitter's Typeahead - jQuery UI

Here you can find a good tutorial for an Autocomplete Field: http://www.pontikis.net/blog/jquery-ui-autocomplete-step-by-step