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

Question

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.

Answer 1

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.