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
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@example.com, 12345
2. Bobby, firstname.lastname@example.org, 9876
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.