How can I search for a person full name if I have first name stored in a column called "first_name" and the last name is stored in a column called "last_name"
Note that this table has couple million records so I need an efficient way to do the search. and I am using MySQL Server
the column first_name and the column last_name are both type VARCHAR(80).
I have tried the following so far which works but slow because it ignores the indexes because of the concat function
SELECT first_name, phone FROM people WHERE CONCAT_WS(' ',first_name, last_name) like '%John Smith%'
I also have tried to add index full text index on( first_name, last_name) and then this query
SELECT * FROM people WHERE MATCH(first_name, last_name) AGAINST('John Smith')
but it is not the fastest query. is there a better approach to this problem?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT people.first_name, people.phone FROM people WHERE CONCAT_WS(' ', people.first_name, people.last_name) LIKE '%John Smith%'