On Wed, 7 May 2003, William Thompson wrote:
> What happens if I do:
> hosts = ${lookup mysql .... }
>
> and the lookup returns say 10,000 rows (also assuming \n is a seperator like
> : is). How would exim handle this? I'm sure on systems with loads of
> memory it wouldn't be a problem.
If the lookup returns 10,000 rows, Exim will try to build a humungously
long string. It wasn't designed with that in mind. I figured that people
should be able to build more selection into their SQL statements....
Let me review your problem. You want
hosts = something
in a MAIL ACL, where "something" depends on the envelope sender domain.
Is that right? (I'm not sure this is all that sensible, given that
senders are trivially forged, but let's ignore that for now.) You can't
say "domains =" because that refers to a recipient domain, and there
isn't a "sender_domains =" condition in an ACL. If there were, you could
say
sender_domains = mysql;...
and it would all be easy. But that isn't available, so you have to do it
the hard way.
Let's suppose there are 10,000 hosts in your list. My recommendation
would be to find a way of rewriting the SQL query so that it returned
just one host, namely, the host in $sender_host_name, or better still,
the value of $sender_host_address so that the comparison can be done
quicker. After all, Exim will do a linear search on 10,000 items,
whereas databases have indexes and things to speed them up, don't they?
Can you not write an SQL query to do that? As I say, I'm no SQL expert,
but presumably you can write a query that extracts your 10,000 records
into a temporary table and then searches those for the matching one and
returns just one value? I'm afraid I don't have time to dig out my MySQL
book to give more detail than that.
> Just off the top of my head, is there an easy way (or possible) to convert
> an @@lsearch* into an sql db?
Pass. My SQL experience isn't up to that off the top of my head.
> If I could have a lookup that uses another lookup, IMO, I don't think that'd
> be a problem (unless reading all rows is).
You can use ${expand to re-expand, and you can use ${extract to extract
data, but I really, really, would not advise returning 10,000 rows, for
performance reasons.
--
Philip Hazel University of Cambridge Computing Service,
ph10@??? Cambridge, England. Phone: +44 1223 334714.
