Top 10 Tricks for SQL Optimization. Part 1

Introduction

In this series of articles, we will look at the top 10 SQL query optimization tricks that will help you increase the speed of query execution, thereby reducing the load on the server. Let's roll))

#1 “regexp_like” > “LIKE”

I strongly recommend replacing the use of the “LIKE” operator with “regexp_like”.

Example:

SELECT *
FROM
    table1
WHERE
    lower(item_name) LIKE '%samsung%' OR
    lower(item_name) LIKE '%xiaomi%' OR
    lower(item_name) LIKE '%iphone%' OR
    lower(item_name) LIKE '%huawei%' OR
   -- и т.д

Replace with:

SELECT *
FROM
    table1
WHERE
    REGEXP_LIKE(lower(item_name), 'samsung|xiaomi|iphone|huawei')

If you are working on a large project and the flexibility of SQL queries is a priority for you, and also if your search criteria are complex and varied, use REGEXP_LIKE is a better choice. Because this operator allows you to use more flexible and dynamic approaches to data filtering.

#2 “regexp_extract” > “Case-when Like”

Another example in which the use of regular expressions provides an advantage in search flexibility and scaling of SQL queries.

Example:

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

Replace with:

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

Usage REGEXP_EXTRACT provides many advantages for data retrieval and text manipulation over the CASE...WHEN LIKEwhich is more suitable for simple conditions. If your task is to extract specific substrings and work with complex patterns, REGEXP_EXTRACT will become a more suitable tool.

#3 JOIN and UNNEST are superior to static lists in SQL

Example:

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

Replace with:

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

Here, using splityou can easily change the list itemidretrieving it from another data source or generating it programmatically. This is useful if itemid changes frequently or is produced at runtime.

This approach may be useful if itemid can be supplied as an array, avoiding hard coding of values. If you need to work with a large amount of data, this design can simplify the processing process.

IMPORTANT. This approach may be less performant for simple cases because it requires additional join operations, which can increase the load on the database.

#4 Using JOIN from larger tables to smaller ones

Example:

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

Replace with:

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

In most SQL engines, the performance of joins does not depend on table order, since the query optimizer can, in most cases, reorder operations to improve efficiency. However, in some cases (like MYSQL), especially when you have large tables and certain indexes, the order can matter. For example, if small_table SIGNIFICANTLY less, the optimizer can choose a more efficient query execution plan.

#5 Dynamic data generation using subqueries

Example:

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

Replace with:

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

In the context of performance and readability, both queries may be adequate depending on the situation. If in table2 There are quite a lot of records and you need additional processing, the second option may be preferable as it provides more flexibility. If the request is simple and “easy”, then the first option may be a good enough option and easier to read. This is my subjective opinion))

I’ll also note that MSSQL and pgSQL easily optimize the first version of the query, so there won’t be much difference

I hope you liked this article). In the second part of the article, 5 more tips will be collected to improve your SQL queries. Good luck everyone!