[Workaround.org] ISPmail for Squeeze?!

Emmanuel Revah stsil at manurevah.com
Tue Jan 25 23:58:00 CET 2011


 On Tue, 25 Jan 2011 22:27:16 +0100, Christoph Haas 
 <email at christoph-haas.de> wrote:
> Am Dienstag, den 25.01.2011, 14:33 +0000 schrieb Emmanuel Revah:
>>  CREATE VIEW view_aliases AS
>>  SELECT virtual_aliases.id, domain_id, source, name, CONCAT(source, 
>> '@',
>>  name) AS email, destination
>>  FROM virtual_aliases
>>  LEFT JOIN virtual_domains ON (virtual_aliases.domain_id =
>>  virtual_domains.id);
>
> The problems with the old database schema were:
>
> 1. CONCAT(…)
>    This lead to a view with string operations which scaled very badly
>    on large installations.
>
> 2. JOIN…
>    Same problem. The JOIN makes it less efficient (although more
>    normalized) and this adds to the scalability issue.
>
> You will not notice that at home or on a small server with 10 domains
> and 500 accounts. But I have received feedback that it was a major
> performance penalty from larger installations.
>
> Cheers
>  Christoph
>


 I can see your point for scalability, I haven't had the chance to test 
 on a big enough setup, as per your feedback it makes sense for the 
 tutorial to maintain the current pattern.. . For me this makes the admin 
 interface much lighter so until I get to the overload situation I'm 
 fine..
 : ]

 Anyway, to the original question about the database from Oliver Ladner, 
 isn't virtual_aliases already along the desired pattern:

> Is there a plan to re-organize?  For example:
>
> table virtual_aliases
>  id  domain_id   source            destination
>  1   1           foo at example.org   bar at example.org
>  2   1           foo at example.org   blah at example.org

 as here: http://workaround.org/ispmail/lenny/preparing-the-database
 ------------------------------------------
 CREATE TABLE IF NOT EXISTS `virtual_aliases` (
   `id` int(11) NOT NULL auto_increment,
   `domain_id` int(11) NOT NULL,
   `source` varchar(100) NOT NULL,
   `destination` varchar(100) NOT NULL,
   PRIMARY KEY  (`id`),
   FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE 
 CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 ------------------------------------------

 Same or am I missing something ?


-- 
 Emmanuel Revah
 http://manurevah.com


More information about the Workaround-chitchat mailing list