[Savannah-hackers-public] libnss-mysql-bg: More efficient query for group members

[Sylvain Beucler]

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.

Best regards,

-- 
Sylvain