How to make this like query more faster?

user_13910368 0
Viewed 80

Currently below query takes 1 sec with 100 data in PostgreSQL.It maybe takes a long time when data comes in a bunch so how can I make this query faster?

Query:

select distinct u.firstname,u.lastname,u.email,u.profile_pic
from user u
where (lower (u.firstname) like '%ab%'
       or lower(u.email) like  '%ab%'   
       or lower(u.lastname) like '%ab%' )
  and u.id != 2 
  and u.active = true;

Thanks in advance.

postgresql sql performance
1 Answers

I have only 2 recomendations for you:

  1. When you are using functions on the fields in were conditions, so recommended to create an expression index (function-based index), if-else your DB can not use indexes. For your query recommended creating lower(lastname) index instead of lastname index (and for firstname and email fields)

  2. For using like '%xxx%' your DB can not use indexes for this field. For using indexes you must write like 'xxx%'. If you needed to use only '%xx%' then recommended using Full-Text Search modules on DB. There are many samples and documentation on the Internet about "How to enable Full-Text Search on PostgreSQL".

In addition, you do not need anything else.

user_172960840
Related