#14 More efficient query for group members

[Beuc]

I reported this bug directory to the maintainer, but I copy it here so it doesn't get reported several times:
http://lists.gnu.org/archive/html/savannah-hackers-public/2009-08/msg00004.html

Hi,

Thanks for developing libnss-mysql, it looks like I'll be able to drop
our db->system cron job ( at http://savannah.gnu.org/ ) :)

I have a suggestion to improve the 'getgrent' query quite a lot. Are
you still maintaining the package?

I noticed that 'getent group' is particularly slow, as libnss-mysql
makes one query per group (this is the only place where it performs
poorly compared to libnss-ldap ;)).

Since I'm using joins in that query this is taking several minutes for
3000 groups (0.2s per query * 3000 = 10mn).

As far as I can see this is a design decision, to get the group
members from in a separate table, with a separate query for each
group.

However it's now possible to easily grab all those members in a single
"getgrent" query, for example

SELECT unix_group_name, 'x', gidNumber,
GROUP_CONCAT(user_name SEPARATOR ',')
FROM user_group
JOIN user ON user.user_id = user_group.user_id
JOIN groups ON groups.group_id = user_group.group_id
GROUP BY groups.group_id;

with a simple many2many relationship:
- user
- user_id PK
- user_name
- uidNumber
- group
- group_id PK
- unix_group_name
- gidNumber
- user_group
- user_id FK
- group_id FK

and GROUP_CONCAT
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

(If GROUP_CONCAT sounds edgy, although it's here since 4.1, it's still
possible to drop the grouping and concat manually in the C code.)

That query would take 0.4s. With the current version 'getent group'
takes several minutes with the joins, and no less than 4s if I cache
the user and group fields in the 'user_group' table (for 3000 groups).

Are you interested in improving the performances of getgrent / 'getent group'?
I'm willing to work on a patch.