Re: (ITS#4605) backs-sql: speedup when using views

On Sat, 2006-07-01 at 08:31 +0000, reiffert@student.physik.uni-mainz.de
wrote:
> Here is what I found out when it's about using views with back-sql
>
> I was playing with back-sql, in detail with mysql when noticing that
> searching over two views or more get's *VERY* slow. So I was
> looking for an replacement of the setup that can be found in
> man back-sql.
>
>
> I finally came to the result of using just *one* view in my mysql-database.
>
> That gave me a strange error that looks like the follwing
> error message of the sql-server log:
>
>
> SELECT DISTINCT ldap_entries.id,ldap_entries.id,'inetOrgPerson' AS
> objectClass,ldap_entries.dn AS dn FROM ldap_entries,ldap_entries WHERE
> ldap_entries.id=ldap_entries.keyval AND ldap_entries.oc_map_id=1 AND 9=9 AND
> 3=3;
>
> and the errormessage was: ERROR 1066 (42000): Not unique table/alias:
> 'ldap_entries'
> as "FROM ldap_entries,ldap_entries" is not working here.
>
>
> So I patched servers/slapd/back-sql/search.c and now everything is working
> at a useable speed for our MUA's (Thunderbird) that use slapd.
>
>
>
> Additional information that can be found at the provided URL:
> - slapd.conf
> - the patch
> - mysql-tables used according to man back-sql
> - ldap_attr_mappings
> - ldap_entry_objclasses
> - the *one* view ldap_entries holding all information
>
>
> Please feel free to ask back.
Your hack appears a misuse of back-sql that breaks its generality. The
fact that you get poor performance out of back-sql, especially when
using views(, especially if your views are not optimized by adding
appropriate indices to keys, but this is out of scope here) is intrinsic
in the design of back-sql. If you need performance, use back-bdb or
back-hdb. If you put everything in one view that hijacks the name of a
table that is required internally by back-sql to work appropriately,
and, as such, should not be altered, then you cannot complain about it
breaking basic implicit join and aliasing rules.
Your issue is the result of a misuse of the software; I suggest your
patch be rejected as it would break back-sql destroying its generality
and versatility. Feel free to use your patched version as a custom
backend that suits your addressbook-only needs, though.
p.
Ing. Pierangelo Masarati
Responsabile Open Solution
OpenLDAP Core Team
SysNet s.n.c.
Via Dossi, 8 - 27100 Pavia - ITALIA
http://www.sys-net.it
------------------------------------------
Office: +39.02.23998309
Mobile: +39.333.4963172
Email: pierangelo.masarati@sys-net.it
------------------------------------------