[TYPO3-dev] Performance of FIND_IN_SET in JOIN condition

[JoH asenau]

Am 20.01.2011 14:20, schrieb Marc Bastian Heinrichs:
> Hey,
>
> does anybody have an information about the performance (or could test it
> in a huge setup) of the following query
>
 > 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.

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