Searching a name from full name

[Nuv]

I have a database something like mentioned below. Please note that this is not the complete table structure. Only the relevant is shown.

 

CREATE TABLE IF NOT EXISTS `student` (
 `id` int(13) NOT NULL AUTO_INCREMENT,
 `title` varchar(3) NOT NULL,
 `firstname` varchar(30) NOT NULL,
 `middlename` varchar(30) NOT NULL,
 `lastname` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

 

 

I'm collecting the name in 3 parts, Firstname , Middlename and Lastname . Now while searching for a name i'm trying to CONCAT all the 3 parts and then search.

 

$sql_name = mysqli_query($con, "SELECT * FROM `student` WHERE CONCAT( firstname,' ', middlename,' ', lastname ) Like '".$search."%'  LIMIT ".$start.", ".$limit."") or die("Error: ".mysqli_error($con));;

 

 

I'm only wondering if there is a better and faster way to achieve this ?

While searching over the web i saw some people using the 'difference' between name and search string and displaying it in descending order, however they weren't using MySql .

[fenway]

Searching with LIKE is terrible on any meaningful amount of data.

 

Using FULLTEXT isn't useful for anything but prose.

 

There are plenty of algorithms you can use -- but what are you trying to achieve? True full-text search, or something else?