We are using Roundup with the MySQL backend for a tracker instance with
almost half a million messages and about 32.000 issues. With this amount
of data, we saw a degradation of performance over time, mainly because
MySQL was unable to answer more complex filter queries without resorting
to table scans and on-disk sorting.

Wondering, why this should be necessary after all, i found the problem
to be the column data types in multilink tables - they are declared as
varchar(255) instead of integer. This, for sure, makes many queries
quite expensive, as e. g. joins require on-the-fly type conversions and
can't make use of the sort order in indexes and the table itself.

In our tracker instance, i changed the type to integer for all the
existing multilink tables, and the performance improvement really makes
a big difference.

Now i would like to ask: Is there any good reason for having linkid and
nodeid declared as a varchar(255) on MySQL and not going with the
default in rdbms_common?

    def create_multilink_table(self, spec, ml):
        sql = '''CREATE TABLE `%s_%s` (linkid VARCHAR(255),
            nodeid VARCHAR(255)) ENGINE=%s'''%(spec.classname, ml,
                self.mysql_backend)
        self.sql(sql)
        self.create_multilink_table_indexes(spec, ml)

Richard, I guess the varchar in multilink tables is has historical
reasons (ID types used to be strings for some time). Do you remember the
reason for special-casing this in the mysql backend? Do you see things
that would break if we change this?

Ralf

Yes, ids stored as strings is a historical artefact going all the way back to when Roundup was 
implemented over Berkley DB style databases. I can think of no reason why the mysql backend 
should be special-cased like it is. Perhaps the mysql backend changelog might shed some light?