[TYPO3-dev] Performance of FIND_IN_SET in JOIN condition

[JoH asenau]

Am 20.01.2011 15:23, schrieb Marc Bastian Heinrichs:
> Hey Joey,
>
>>   >  SELECT fe_users.* FROM fe_users LEFT JOIN fe_groups ON (
>>   >       fe_users.usergroup LIKE CONCAT ('%', fe_groups.uid, '%')
>>   >       OR fe_users.usergroup LIKE CONCAT ('%', fe_groups.uid)
>>   >       OR fe_users.usergroup LIKE CONCAT ('%', fe_groups.uid)
>>   >       OR fe_users.usergroup = fe_groups.uid
>>   >  ) WHERE fe_groups.uid IN(...)
>>
>> Just in case that FIND_IN_SET should not be used due to whatever reason:
>> Shouldn't this be
>>
>> SELECT fe_users.* FROM fe_users LEFT JOIN fe_groups ON (
>> 	CONCAT (',' , fe_users.usergroup , ',') LIKE
>> 	CONCAT ('%,' , fe_groups.uid, ',%')
>> ) WHERE fe_groups.uid IN(...)
>>
>> anyway? Otherwise you would find a uid value of 1 in a usergroup value
>> of 123 as well, which is not the desired result I guess.
>
> you are right. The statement is not correct. I just copied it from the
> tracker. The correct one should be
>
> SELECT fe_users.* FROM fe_users LEFT JOIN fe_groups ON (
>   	fe_users.usergroup LIKE CONCAT ('%,' , fe_users.uid , ',%')
>   	OR fe_users.usergroup LIKE CONCAT ('%,' , fe_groups.uid)
>   	OR fe_users.usergroup LIKE CONCAT (fe_groups.uid, ',%')
> 	OR fe_users.usergroup = fe_groups.uid
>   ) WHERE fe_groups.uid IN(...)

Still one "CONCAT" and 3 "OR"  too much ;-)
Although I don't know, if this would speed up the query significantly.

Cheers

Joey

-- 
Wenn man keine Ahnung hat: Einfach mal Fresse halten!
(If you have no clues: simply shut your gob sometimes!)
Dieter Nuhr, German comedian
Xing: http://contact.cybercraft.de
Twitter: http://twitter.com/bunnyfield
TYPO3 cookbook (2nd edition): http://www.typo3experts.com