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, jon@gmail.com, 12345
2. Bobby, jon-bobby@gmail.com, 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.
Best Answer
If you need to increase performance you can do the following:
Add FULLTEXT index to the columns name, email and 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 = 3
to index words up to 3 chars.