[TYPO3-dev] Performance of FIND_IN_SET in JOIN condition

[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(...)


Regards
Marc Bastian