How to optimization this query??

HyeontaeJu

My query has the many 'OR' clause cause of this clause the query speed is so low.. is it possible optimization??

SELECT
a.image_link ,
a.product_id ,
b.title ,
b.mall_name ,
b.brand ,
b.category_name1 ,
b.category_name2 ,
b.category_name3 ,
b.category_name4
FROM
(
SELECT
product_id, image_link, image_status
FROM
newbiz_product_dev.products_image
ORDER BY
product_id )a
LEFT OUTER JOIN (
SELECT
product_id , title , mall_name , brand , category_name1 , category_name2 , category_name3 , category_name4
FROM
newbiz_product_dev.products
ORDER BY
product_id )b ON
a.product_id = b.product_id
WHERE
a.image_status = 'SUCCESS'
AND ( b.title LIKE '%' || 'test' || '%'
OR b.mall_name LIKE '%' || 'test' || '%'
OR b.brand LIKE '%' || 'test' || '%'
OR b.category_name1 ILIKE '%' || 'test' || '%'
OR b.category_name2 ILIKE '%' || 'test' || '%'
OR b.category_name3 ILIKE '%' || 'test' || '%'
OR b.category_name4 ILIKE '%' || 'tset' || '%' )
LIMIT 50 OFFSET 0;

Bryan_H

Have a look at Vertica text indexing to search for strings in fields: https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/Tables/TextSearch/TextSearchConceptual.htm