SQL Many Like%

#1

Hello !
I want to optimize my SQL query that is using many ‘like%’ to use one IN
This is my example working but it is not optimized

select * from tableau where column1 like '%11223%' or
                                           column1 like '%445566%' or
                                           column1 like '%778899%' ;

I want to replace the use of or because there is still many values
Thanks in advance

#2

Unless column1 only has 112233, 445566 or 778899 in the fields, you can’t use an IN.

IN replaces multiple equalities. In other words

SELECT field1
  FROM tableau
 WHERE column1 IN ('112233', '445566', '778899')

Is the same as

SELECT field1
  FROM tableau
 WHERE column1 = '112233'
    OR column1 = '445566'
    OR column1 = '778899'
#3

I have not tried this, but it maybe works (or something like this):

select * from tableau where column1 like in('%11223%','%445566%,'%778899%')

#4

Thanks but I am looking for solution works with like %

#5

Well , that causes syntax error

#6

What values specifically are you searching for? You may be able to condense the list with tactical pattern usage, rather than language constructs.

#7

you definitely should!!!

#8

Reference which is number with the size of clothes
I am working on Magento

#9

The only option I can think of that could work with the case you present into one clause, is REGEX. Though I am not certain if there will be any speed gains by doing this.
https://dev.mysql.com/doc/refman/5.7/en/regexp.html#operator_regexp

Most probably you will have much better gains, by taking a look on what your trying to do, and then refactoring it, making a better solution possible.

2 Likes
#10 
SELECT 
	name
FROM
	test
WHERE
	name LIKE IN ('%i%', '%o%')

definitely doesn’t work.

SELECT 
	name
FROM
	test
WHERE
	name LIKE '%i%'
OR 
	name LIKE '%o%'

Definitely does work and produces the results I expected.

Can you live with that?

#11

Thanks but as I mentionned below I want to get optimised solution
Thanks in advance

#12

okay, try this

SELECT * FROM tableau WHERE column1 LIKE '%11223%' 
UNION ALL
SELECT * FROM tableau WHERE column1 LIKE '%445566%' 
UNION ALL
SELECT * FROM tableau WHERE column1 LIKE '%778899%'

the only ~real~ way to optimize your query is to remove the % at the front of the LIKE string

#13

I’m curious, is the column1 field datatype text?

Can you cast the value to a numeric?

#14

It is the sku code , I am working on Magento exactly version 1.9
Thanks in advance

#15

It sounds like something where you may be able to use SUBSTRING and lose the leading wildcard. Does what you want always start the same number of characters in?

#16

SUBSTRING is no more sargable than a leading LIKE wildcard

1 Like
#17

Running an ALTER TABLE to ADD a new field that is only the desired portion of the value seems worth considering.

Another option is to shift the load from the database to the client language. Or live with the query as it is with wildcards.

#18

I want to optimize it not letting it like that
Thanks in advance

#19

did you try my UNION solution?

#21

It did not differ from my solution, same number of lines & instructions