Optimized SQL Queries for Daily Use !

1. Use ‘regexp_like’ to replace ‘LIKE’ clauses

Normal Query -

SELECT *
FROM
table1
WHERE
lower(item_name) LIKE '%Table%' OR
lower(item_name) LIKE '%Chair%' OR
lower(item_name) LIKE '%Bed%' OR
lower(item_name) LIKE '%Fan%'
--and so on

Optimized Query -

SELECT *
FROM
table1
WHERE
REGEXP_LIKE(lower(item_name), 
'Table|Chair|Bed|Fan')

2. Use ‘regexp_extract’ to replace ‘Case-when Like’

Normal Query -

SELECT
CASE
WHEN concat(' ',item_name,' ') LIKE '%acer%' then 'Acer'
WHEN concat(' ',item_name,' ') LIKE '%advance%' then 'Advance'
WHEN concat(' ',item_name,' ') LIKE '%alfalink%' then 'Alfalink'
…
AS brand
FROM item_list

Optimized Query -

SELECT
regexp_extract(item_name,'(asus|lenovo|hp|acer|dell|zyrex|...)') 
AS brand
FROM item_list

3. Convert long list of IN clause into a temporary table.

Normal Query -

SELECT *
FROM Table1 as t1
WHERE
itemid in (3363134, 
5189076, …, 4062349)

Optimized Query -

SELECT *
FROM Table1 as t1
JOIN (
SELECT
itemid
FROM (
SELECT
split('3363134, 5189076, …,', ', ')
as bar
)
CROSS JOIN
UNNEST(bar) AS t(itemid) 
) AS Table2 as t2
ON
t1.itemid = t2.itemid

4. Always order your JOINs from the largest tables to the smallest tables.

Normal Query -

SELECT
*
FROM
small_table
JOIN
large_table
ON small_table.id = large_table.id

Optimized Query -

SELECT
*
FROM
large_table
JOIN
large_table
ON small_table.id = large_table.id

5. Use simple equi-joins

Normal Query -

SELECT *
FROM
table1 a
JOIN
table2 b 
ON a.date = CONCAT(b.year, '-', 
b.month, '-', b.day)

Optimized Query -

SELECT *
FROM
table1 a
JOIN (
select
name, CONCAT(b.year, '-', b.month, '-', b.day) as date
from
table2 b 
) new 
ON a.date = new.date

6. Always "GROUP BY" by the attribute/column with the largest number of unique entities/values

Normal Query -

select
main_category,
sub_category,
itemid,
sum(price)
from
table1 
group by
main_category, sub_category, itemid

Optimized Query -

select
main_category,
sub_category,
itemid,
sum(price)
from
table1 
group by
itemid, sub_category, main_category

7. Avoid subqueries in WHERE clause

Normal Query -

select
sum(price) 
from
table1 
where
itemid in (
select itemid
from table2 
)

Optimized Query -

with t2 as (
select itemid
from table2 
)
select
sum(price) 
from
table1 as t1 
join
t2 
on t1.itemid = t2.itemid

8. Use Max instead of Rank

Normal Query -

SELECT *
from (
select
userid,
rank() over (order by prdate desc) as rank
from table1
)
where ranking = 1

Optimized Query -

SELECT userid, max(prdate)
from table1
group by 1