Sfrgttk

Do UK voters know if their MP will be the Speaker of the House?

Do scales need to be in alphabetical order?

Short story with a alien planet, government officials must wear exploding medallions

Why are the 737's rear doors unusable in a water landing?

Expand and Contract

How can saying a song's name be a copyright violation?

Can a virus destroy the BIOS of a modern computer?

Cursor Replacement for Newbies

Should I cover my bicycle overnight while bikepacking?

What is a romance in Latin?

How do I gain back my faith in my PhD degree?

Bullying boss launched a smear campaign and made me unemployable

Why no variance term in Bayesian logistic regression?

Detention in 1997

Im going to France and my passport expires June 19th

Venezuelan girlfriend wants to travel the USA to be with me. What is the process?

How does a predictive coding aid in lossless compression?

How can I determine if the org that I'm currently connected to is a scratch org?

Size of subfigure fitting its content (tikzpicture)

I would say: "You are another teacher", but she is a woman and I am a man

How badly should I try to prevent a user from XSSing themselves?

How did the Super Star Destroyer Executor get destroyed exactly?

Apex Framework / library for consuming REST services

Could the museum Saturn V's be refitted for one more flight?

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

What MySQL storage engine and table schema should I use?MySQL Workbench - question about creating FK relationshipsSQL select default parameterCan we use Varchar for columns that will save only numbersmultiple MYSQL full text indexing for innodb and memory + disk efficiencyModeling a database structure for contact managementStoring Employee AccountsMYSQL Multiple table optimization for advanced searchHow to find out if entity is used in relations somewhere in database?Which one of these two contact management database designs is correct or best practice?

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, 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.

mysql

share

asked 6 mins ago

sureshbabusureshbabu

1

New contributor

sureshbabu is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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, 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.

mysql

share

asked 6 mins ago

sureshbabusureshbabu

1

New contributor

sureshbabu is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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.

mysql

share

asked 6 mins ago

sureshbabusureshbabu

1

New contributor

sureshbabu is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

SELECT * 

FROM Employee 

where employer_id = 1 

and LOWER(name) like '%jo%' 

or LOWER(email) like '%jo%' 

or LOWER(phone) like '%jo%'

share

asked 6 mins ago

sureshbabusureshbabu

1

New contributor

sureshbabu is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 6 mins ago

sureshbabusureshbabu

1

New contributor

sureshbabu is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 6 mins ago

sureshbabusureshbabu

1

New contributor

sureshbabu is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 6 mins ago

sureshbabusureshbabu

1