0

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.

1
  • A suitable COLLATION is usually better than using LOWER or UPPER.
    – Rick James
    Apr 21, 2019 at 2:19

1 Answer 1

0

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.

1
  • This fails when an email search is made i.e when we use @ character. Do you know how we can resolve this?
    – SBM
    Apr 10, 2019 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.