optimizing back-sql for naming attribute search

[Brad Midgley <bmidgley@xmission.com>]

Hi

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,
   ldap_entries.keyval,
   ('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?

Brad