Developers of Sympa

[Olivier Salaun <address@concealed>]

Hi Petr,

Using a dedicated table is not a good idea to my mind because :

        * it will have a big impact on the actual code
          (get_which(), ...)
        * you will need to create another TABLE for each 
          big lists you need
        * Sympa will still perform SELECT with a WHERE clause
          on the dedicated table (select a subscriber, bounces,
          ...)

I'd rather suggest you optimize your database/table structure.
I can think about a few things, other sympa-users might have
other «tricks» :
        * Idexes : the subscriber_table already has an index
          on 'list_subscriber, user_subscriber'
        * Table format : recent versions of MySQL use a more
          efficient table format (MYISAM). You should convert
          your tables to this format using mysql_convert_table_format.
          'isamchk' utility could also reorganize your data in table
        * Changing 'list_subscriber' from VARCHAR to CHAR should
          fasten queries.
        * check /usr/share/mysql/my-huge.cnf sample config
          It has huge buffers and caches


Hope it helps...

Petr Prazak wrote:

>  I'd like to propose an optimalization to Sympa.
> 
>  I'd would be good to allow a list to contain its subscribers in a
>  separate table rather than to contain all subscribers in one table.
> 
>  For very large lists, there a difference in accessing a table with or
>  without "where" clause.
> 
>  I've got several list that are small, and one very large list.
> 
>  For example:
> 
> mysql> select count(*) from subscriber_table;
> +----------+
> | count(*) |
> +----------+
> |   789468 |
> +----------+
> 1 row in set (0.00 sec)
> 
> mysql> select count(*) from subscriber_table where 
> list_subscriber='large_list';
> +----------+
> | count(*) |
> +----------+
> |   782848 |
> +----------+
> 1 row in set (52.43 sec)
> 
> You can see, that offloading this huge list to the separate table would
> help a performance.
> 
> Or do you think using a different DB would help ?
> 
> What is your opinion ?


--
Olivier Salaün
Comité Réseau des Universités