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
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'
I have not tried this, but it maybe works (or something like this):
select * from tableau where column1 like in('%11223%','%445566%,'%778899%')
Thanks but I am looking for solution works with like %
Well , that causes syntax error
What values specifically are you searching for? You may be able to condense the list with tactical pattern usage, rather than language constructs.
Reference which is number with the size of clothes
I am working on Magento
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
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?
Thanks but as I mentionned below I want to get optimised solution
Thanks in advance
r937
November 27, 2019, 9:52am
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
I’m curious, is the column1
field datatype text?
Can you cast the value to a numeric?
It is the sku code , I am working on Magento exactly version 1.9
Thanks in advance
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?
r937
November 28, 2019, 9:39am
16
SUBSTRING is no more sargable than a leading LIKE wildcard
1 Like
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.
I want to optimize it not letting it like that
Thanks in advance
r937
November 28, 2019, 6:06pm
19
ahmedchouihi:
I want to optimize it
did you try my UNION solution?
It did not differ from my solution, same number of lines & instructions