I'm working on a project now where I have to create an extensive filtering. Users belong to a number of groups and I should process requests when user belongs to several groups. Say find users that are both in golf club and in beer club. (see case #3 here)
There is a solution there too. But I feel it is not optimal. I think there may be something better.
All ideas/suggestions are welcome. Both here and on the blog
Thanks for help beforehand!
u.id = ug1.userID AND ug1.groupID=7
u.id = ug2.userID AND ug2.groupID=15
Uses JOINs instead of subqueries, should be faster. Especially if UserGroup.groupID and UserGroup.userID both have an index.
COOL!!!! Besides speed it is more simple to form such query!!!
Maybe there even more efficient ways of doing this? I'm OK to change DB structure, but I want to preserve the current flexibility.
I don't see why you should change the DB structure to perform this query, or am I missing something?
No, I meant that I'm OK to redesign DB if needed in order to achieve the same result more efficiently. For example, I know I can store hierarchy more efficiently with additional fields that allow me to get all subtree of a node with the single query.
I thought that there may be other optimizations similar to this one for my task.