How to add indexing for a search based on multiple columns in MySQL table

Question

I am adding a search button with the suggestions displayed while we type on it similar to google and I need to search multiple columns in a single table. How to add indexing for that columns?

I have two tables in my application named as Employer and Employee. I want to add a search box so that the Employer can search employees of only his organisation based on their name or email or phone number. When the employers types on the search box I need to show the suggestions below. For Ex. when employer types Jo on the text box, I will show suggestions as

1. Jon, [email protected], 12345

2. Bobby, [email protected], 9876

3. .....

The Employee table has columns id, name, email, phone, employer_id fk references Employer table.

What I think the query might be is

SELECT * 
FROM Employee 
where employer_id = 1 
and LOWER(name) like '%jo%' 
or LOWER(email) like '%jo%' 
or LOWER(phone) like '%jo%'

I want to know whether this is a valid query to use. I am not sure about its performance. Also I am not sure how to add index to this table in this particular use case.

I would really appreciate your valuable suggestions.

• Rick James: A suitable COLLATION is usually better than using LOWER or UPPER.

Answer 1

If you need to increase performance you can do the following:

Add FULLTEXT index to the columns name, email and phone

ALTER TABLE Employee ADD FULLTEXT (name), ADD FULLTEXT (email), ADD FULLTEXT (phone)

Dont use select * ( no caching )

select name, email, phone , ( 10 * (MATCH (name) AGAINST ("Jon*" IN BOOLEAN MODE)) + 8 * (MATCH (email) AGAINST ("Jon*" IN BOOLEAN MODE)) + 2 * (MATCH (phone) AGAINST ("Jon*" IN BOOLEAN MODE)) ) as points from Employee HAVING points > 1 order by points DESC LIMIT 10

In this solution you get an result in a better order of your results. If your keyword match with name, it is more helpfull to show than a match with phone.

You can change your my.cnf and add: ft_min_word_len = 3to index words up to 3 chars.

• SBM: This fails when an email search is made i.e when we use @ character. Do you know how we can resolve this?