how to optimize SQL sub queries?

Question

This my scenario I tried to search a record in the SQL table using the name. So, I tried to create a subquery and I used like operator also in Postgres. SQL query It's working fine. but it's taking so much time. So, I checked why it's taking so much time. the reason is the subquery. In the subquery it hitting all the records in the table. How to optimize subquery.

SQL QUery

SELECT
id, latitude,longitude,first_name,last_name,
contact_company_id,address,address2,city,state_id, zip,country_id,default_phone_id,last_contacted,image,contact_type_id
FROM contact
WHERE company_id = 001
AND contact_company_id IN (select id from contactcompany where lower( name ) ~*'jack')

So, I tried to run this query it's taking 2 seconds and it hit all records in the contact company table that only It's takes time. How to optimize subquery using SQL?

• Gordon Linoff: I removed the inconsistent database tags. Please tag only with the database you are really using (which I would guess is Postgres based on the syntax, so I left that).
• Yann39: Consider joining instead of using a subquery, then be aware that using lower(name) will make any index on name useless.
• dnoeth: Your pattern matching disallows using an index, how big is the contactcompany table? And lower( name ) is useless as ~*'jack' already applies case-insensitiv comparison

Answer 1

Please try a sub query as a inner join with a main table, both query give same result.

Example here :

SELECT contact.id, 
 contact.latitude,
 contact.longitude,
 contact.first_name,
 contact.last_name,
 contact.contact_company_id,
 contact.address,
 contact.address2,
 contact.city,
 contact.state_id, 
 contact.zip,
 contact.country_id,
 contact.default_phone_id,
 contact.last_contacted,
 contact.image,
 contact.contact_type_id
FROM contact As contact
Inner Join contactcompany As contactcompany On contactcompany.id = contact_company_id
WHERE company_id = 001
AND lower( name ) ~*'jack'

Answer 2

I would start by writing the query using exists. Then, company_id is either a string or a number. Let met guess that it is a string, because the constant is represented with leading zeros. If so, use single quotes:

SELECT c.*
FROM contact c
WHERE company_id = '001' AND
 EXISTS (SELECT 1
 FROM contactcompany cc
 WHERE cc.name ~* 'jack' AND
 cc.id = c.contact_company_id
 );

Then an index on contact(compnay_id, contact_company_id) makes sense. And for the subquery, contactcompany(id, name).

There may be other alternatives for writing the query, but your question has not provided much information on table sizes, current performance, or the data types.

• Hariprasath Vengatachalam: Can you please explain why use select 1
• Gordon Linoff: @HariprasathVengatachalam . . . It is easy to type. The subquery needs to return something in most databases (Postgres is an exception), so I always put 1 there. An empty select just looks wrong, even in Postgres.