[Pdns-users] specifying more complex sql queries with gmysql back-end

[Peter van Dijk]

Hi Tom,

On Nov 14, 2011, at 11:56 , Tom wrote:

> I just want to write this in to the config file rather than calling it as a stored procedure, and I'm hoping then that I can reduce load of the database server by actually having MySQL cache the results of the queries (which it doesn't do at all when you call a stored proc).

I should point out that PowerDNS also has a backend query cache, which not only does not care whether you were using a stored procedure, but also has the benefit of not expiring all content on any UPDATE. The negative effect stored procedures have on the MySQL query cache could be mostly mitigated by the PowerDNS query cache.

Another suggestion: a query of the form

   select content,ttl,prio,type,domain_id,name from (select content,ttl,prio,type,domain_id,name,'%s' qtype, '%s' qname from records) t where type=qtype and name=qname;

assigns the two PowerDNS variables to names within the query; presumably you could expand this idea to cover your whole query. It will not be pretty though.

I suggest testing and benchmarking how well a stored procedure would work for your situation. I'm curious to see what comes out :)

Kind regards,
Peter van Dijk