How to write "replace" once in a SQL query having 3 times like operator

Question

Here is my sql query. I don't want to write the "replace" 3 times. How can I optimize it ?

select * from table1 where col1='blah' AND 
(
replace(replace(col2,'_',' '),'-',' ') LIKE ? OR 
replace(replace(col2,'_',' '),'-',' ') LIKE ? OR 
replace(replace(col2,'_',' '),'-',' ') LIKE ?
)

Do you have `INDEX(col1)`? – Rick James Jul 06 '19 at 20:36 — Rick James, Jul 06 '19 at 20:36

Lukasz Szozda · Answer 1 · 2019-07-06T07:49:38.947

2

You could use subquery:

SELECT *
FROM (
  select *, replace(replace(col2,'_',' '),'-',' ') AS r
  from table1 
  where col1='blah' 
) s
WHERE r LIKE ? OR r LIKE ? OR r LIKE ?

Or LATERAL:

select *
from table1
  ,LATERAL(SELECT replace(replace(col2,'_',' '),'-',' ')  AS r) s
where col1='blah' 
  and (s.r LIKE ? OR s.r LIKE ? OR s.r LIKE ?)

db<>fiddle demo

I prefer the second approach because there is no need for introducing outer query. This feature was added in version 8.0.14.

answered Jul 06 '19 at 07:42

Lukasz Szozda

162,964
23
234
275

Is LATERAL supported in mysql ? – Hari Das Jul 06 '19 at 07:49
1

@HariDas Of course it is :) Please check provided link and demo – Lukasz Szozda Jul 06 '19 at 07:49
Will writing 3 times, i.e my original query will be faster or the 2nd query you wrote will be faster ? – Hari Das Jul 06 '19 at 07:52
@HariDas "faster" is relative. You should compare execution plans for both queries. Query optimizer could do a lot of transformations depending on order of execution(normally it would calculate the function once per row). From developer perspective `LATERAL` is much better: DRY principle. – Lukasz Szozda Jul 06 '19 at 07:55

score 1 · Accepted Answer · answered Jul 06 '19 at 13:41

1

In MySQL you can use a column alias in the HAVING clause even without any aggregation:

select *, replace(replace(col2,'_',' '),'-',' ') as col2_replace
from table1
where col1='blah'
having col2_replace like ?
    or col2_replace like ?

answered Jul 06 '19 at 13:41

Paul Spiegel

30,925
5
44
53

score 0 · Answer 3 · answered Jul 06 '19 at 11:34

MySQL has a tendency to materialize subqueries -- not only is this overhead for reading and writing a temporary table but it can also affect the use of indexes in a more complicated query.

Here are three alternative solutions that do not require subqueries.

If ? does not contain wildcards, then the simplest method is:

 replace(replace(col2, '_', ' '), '-', ' ') in (?, ?, ?)

If it does, then change logic to use a single regular expression pattern:

 replace(replace(col2, '_', ' '), '-', ' ') regexp ?

You can also explicitly adjust the pattern in the query:

 replace(replace(col2, '_', ' '), '-', ' ') regexp
    concat('(',
           replace(replace(?, '_', '.'), '%', '.*'), ')|(',
           replace(replace(?, '_', '.'), '%', '.*'), ')|(',
           replace(replace(?, '_', '.'), '%', '.*'), ')'
          )

How to write "replace" once in a SQL query having 3 times like operator

3 Answers3