[Date Prev][Date Next]
[Chronological]
[Thread]
[Top]
Re: back-sql sqlquery
Hello again,
On Wed, Oct 19, 2005 at 11:27:29AM +0200, Thomas Hoth wrote:
> On Wed, Oct 19, 2005 at 10:14:55AM +0200, Pierangelo Masarati wrote:
> >
> > > Hello,
> > >
> > > Why do back-sql querys like
> > > (2=2 OR (ldap_entries.id=ldap_entry_objclasses.entry_id AND
> > > ldap_entry_objclasses.oc_name='" /* '))
> > >
> > > For what is the 2=2 ? This makes my query very slow.
> >
> > The "2=2" is a placeholder for "TRUE". Different numbers indicate
> > different places in the code where the replacement took place. I'd be
> > much more worried about the "ldap_entry_objclasses.oc_name='" /* '" which
> > clearly indicates an error in the code, or some abnormal behavior at
> > least. Unfortunately you don't provide enough info to track where it
> > might have happened.
>
> I think the "ldap_entry_objclasses.oc_name='" /* '" ist not a bug. It
> will fill out at the runtime the full query is:
>
> SELECT DISTINCT ldap_entries.id,groups.id,text('posixGroup') AS
> objectClass,ldap_entries.dn AS dn FROM ldap_entries,groups,ldap_entry_objclasses,posix_account,groups_users
> WHERE groups.id=ldap_entries.keyval AND ldap_entries.oc_map_id=4 AND 9=9 AND groups_users.memberuid=posix_account.uidnumber AND
> groups_users.gidnumber=groups.gidnumber AND ((2=2 OR (ldap_entries.id=ldap_entry_objclasses.entry_id AND
> ldap_entry_objclasses.oc_name='posixGroup')) AN ((posix_account.uid='lml34') OR 7=7));
>
> It's from line 738 in servers/slapd/back-sql/search.c
> Iam not an SQL-geek but what ist the sense to ask "true OR query" ?
someone tells me this:
16:51 *»* SELECT ldap_entries.id,groups.id,text('posixGroup') AS
objectClass,ldap_entries.dn AS dn FROM +ldap_entries,groups,ldap_entry_objclasses,posix_account,groups_users
WHERE groups.id=ldap_entries.keyval AND ldap_entries.oc_map_id=4 AND 9=9 AND
+groups_users.memberuid=posix_account.uidnumber AND groups_users.gidnumber=groups.gidnumber AND ((2=2) AND
((upper(posix_account.uid)='LML23') OR 7=7));
16:51 ** omg
16:52 *»* 14 Seconds, originally says SELECt DISTINCT -> 900 seconds
16:52 ** its combining 5 tables in an outside join
16:52 *»* yes
16:53 *»* my conclusion 2h ago had been: rewrite the query generator. I fear this is still true
16:54 ** yes and whoever re-writes it needs to understand joins properly
16:55 *»* this might be a problem, then. :->
16:58 ** this I think is the same query written with joins
select ldap_entries.id,groups.id,text('posixGroup') AS
objectClass,ldap_entries.dn AS dn FROM ldap_entries join groups on
groups.id = ldap_entries.keyval join groups_users on
groups_users.gidnumber=groups.gidnumber join posix_account on
posix_account.uidnumber = groups_users.memberuid where
ldap_entries.oc_map_id=4 AND (upper(posix_account.uid)='LML23')
17:04 *»* a) it runs only a split of a second
17:05 ** does it return the same results ?
17:05 *»* b) it gives only 3 line sof results, the other query gave
these 3 lines over and over
17:05 *»* so after the distinct it is the same
Iam not an C - programmer, but my question is it is possible to rewrite the
query generator to get faster sql?
Thanks
Thomas