[Date Prev][Date Next] [Chronological] [Thread] [Top]

optimizing back-sql for naming attribute search


If I do the most common type of search, by naming attribute:

ldapsearch -H ldaps://iceman.uen.org -D uid=bmidgley,dc=my,dc=uen,dc=org -x -W -d 256 -z 10 "(uid=bmidgley)"

The join between ldap_entries (a view with some filtering logic) and our educator table ends up being quite expensive. Would it be pretty safe to avoid the join since the naming attribute is in the dn? Does it seem a little strange to have the naming attribute there in the dn and also spelled out inside ldap_attr_mappings?

The difference in search times goes from about 14 seconds to 2 seconds on our devel sql server. The new search should be under 1s on the production server.

So here's the current generated search... uid gets mapped to username inside educator:

SELECT DISTINCT ldap_entries.id,utahlink..educator.teacher_id,('inetOrgPerson') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,utahlink..educator WHERE utahlink..educator.teacher_id=ldap_entries.keyval AND ldap_entries.oc_map_id=1 AND upper(ldap_entries.dn) LIKE '%DC=MY,DC=UEN,DC=ORG' AND (upper(username)='BMIDGLEY')

And this is what I am thinking I could recode back-sql to generate when it notices the search is on the naming attr:

SELECT DISTINCT ldap_entries.id,
   ('inetOrgPerson') AS objectClass,
   ldap_entries.dn AS dn
   FROM ldap_entries
   WHERE ldap_entries.oc_map_id = 1
     AND UPPER(ldap_entries.dn) = 'UID=BMIDGLEY,DC=MY,DC=UEN,DC=ORG'

Would that be generally useful?