RE: SQL optimizations (was RE: Successful LDAP signing test)

Hi Murray,
At 10:55 23-02-10, Murray S. Kucherawy wrote:
>If we presume opendkim itself doesn't know what's going on inside
>opendkim-db, the code complexity involves: Determine if the KeyTable
>and SigningTable are actually two tables in the same database on the
>same server, and if so, do the SigningTable query as a SELECT/JOIN
>and cache the results so the KeyTable query just uses them; in all
>other cases, issue two independent queries.
>
>I spoke to the DBA and two other developers here for some opinions
>on this issue. Consensus here is that the added code complexity
>isn't worth the value this optimization would bring. If we presume
>the database being queried is local and properly indexed, the
>round-trip time for the two queries will be negligible, and that's
>where the real cost is; the lookup itself, even though it's doubled,
>is still very cheap.

Query connect time is an overhead which is not even a consideration
in most cases. The "optimization" (JOIN) ensures
consistency. Running two queries means that you are doubling the
number of queries. Again, this is a non-issue in most cases.

The query used is:

   SELECT %s FROM %s WHERE %s = '%s'

We assume that it will return one record. If there is more than one
record, we do not process the other records.

A KeyTable can be used simply as a KeyTable or as part of a system
that has an historic of the selectors and pushes DNS updates. For
example, I may have a date range for which the public key is
valid. If I store the data in the key table, I'll do a sort to get
the latest key that is valid. I cannot have such a functionality
with the above query as the SELECT is hard coded. This is not an
argument to change the current behavior. I'm thinking of I would
extend the SQL functionality. I could always have a workaround where
I use a view to get a KeyTable with the required records.

Regards,
-sm
Received on Tue Feb 23 2010 - 21:59:17 PST