[TYPO3-core] RFC: Bug #9442: SQL LIKE wildcards are not escaped correctly for list queries

[bernd wilke]

Oliver Hader schrieb am Mon, 29 Sep 2008 11:38:13 +0200:

[...]
> New version:
> listQuery('field', 'my%Tag', 'table'); -> ... WHERE (field LIKE
> '%,my\%Tag,%' OR field LIKE 'my\%Tag,%' OR field LIKE '%,my\%Tag' OR
> field = 'my%Tag') -> as you can see we have two patterns "my\%Tag" for
> LIKE and "my%Tag" for direct matching
> 
> The long LIKE condition could be reduced by using a REGEXP like this: ->
> ... WHERE field REGEXP '(^|,)my%Tag(,|$)'
> 
> I've tested the speed of the queries with 10.000 records in a table. The
> LIKE statement finished after 13ms - the REGEXP finished after 36ms.
> 
> olly

I have found another query with less 'likes':

WHERE (conact(',',concat(field,',')) LIKE '%,my\%Tag,%')
or 
WHERE (conact(',',field,',') LIKE '%,my\%Tag,%')
(only mysql?)

can you check times for these?

bernd
-- 
http://www.pi-phi.de/t3v4/cheatsheet.html