[TYPO3-core] RFC #14818: Performance: Use mysql find_in_set-function in t3lib_db->listQuery()

[caspar at gosign.de]

>> I forgot to attach the patch, sorry
>>
>>> This is an SVN patch request.
>>>
>>> Type: Performance
>>>
>>> Bugtracker references:
>>> http://bugs.typo3.org/view.php?id=14818
>>>
>>> Branches:
>>> Trunk
>>>
>>> Problem:
>>> The function listQuery() uses a like-construct to find a value in a
>>> comma-seperated list.
>>> Mysql has a internal function for this called 'find_in_set'
>>> This is 2.5 times faster.
>>>
>>> Solution:
>>> change the t3lib_db->listQuery(), using the mysql-function
>>> find_in_set
>>> instead of the like-construct.
>>> adding the funcion listQuery() to the dbal ux_t3lib_db class with the
>>> like-construct.
>>>
>>> Greetings
>>> Caspar
>
> Casper,
>
> I had a small discussion with Xavier this morning about
> a find_in_set compatible function for PostgreSQL.
>
> Is it possible that the functions get's changed to :
>
> $where = 'find_in_set(\'' . $pattern . '\',' . $field . ') != 0';
>
> The rationale behind this is that it's easer to port
> this function to other databases because for example
> PostgreSQL 'wants' a boolean evaluation, and mysql
> fakes integer for boolean, a bit like what PHP does (incorect IMHO).
> It's also to show other people reading the code the correct way to do
> it.
>
> However, I leave the final decision up to you and Xavier (or others).
>
> Ries
>

Good morning,

I have checked the changed function and I found no reason why we should
not use it this way.

Due to the fact that the find_in_list function does not work properly if
the value contains a comma (,), I have added an InvalidArgumentException
to the listQuery()

Caspar
-------------- next part --------------
A non-text attachment was scrubbed...
Name: bug_14818_2.patch
Type: text/x-patch
Size: 2979 bytes
Desc: not available
URL: <http://lists.typo3.org/pipermail/typo3-team-core/attachments/20100704/88b5c701/attachment.bin>