Custom fields schema

[Sean McAfee]

Myk Melez <myk at mozilla.org> wrote:
>Sean McAfee wrote:
>>Fields-as-columns may make life easier for the programmer, but it's the
>>needs of the users that should be coming first.

>Agreed, and that's why I'm suggesting it, not to make our lives easier, 
>especially considering I think fields-as-columns is harder to develop.  
>Users would be better served by an app living on top of a database which 
>uses the optimum architecture for custom fields.  For my money, that's 
>the relational model developed over the last several decades for the 
>purpose, and which we already use for standard fields.

Well, here's some hard data.

Attached is a Perl program, construct-tables.pl.  It creates two tables,
REALCOL and DISTCOL.  REALCOL has two columns, BUG_ID and CF_FOO, and is
meant to be a simplification of a BUGS table with a single
custom-field-as-real-column called FOO.  DISTCOL has three columns, BUG_ID,
FIELD_ID, and VALUE, with an index on FIELD_ID, and is essentially identical
to my proposed CF_SHORTSTRING table.  The program then populates both tables
with data for 300,000 bugs.  For each bug, it constructs a string of ten
joined words randomly chosen from /usr/share/dict/words, inserting the
string "meta" at a random position in the middle .1% of the time.  The bugs
are inserted into DISTCOL with a field_id that starts at 0 and increases by
one each time, going back to zero when it reaches 100.  The string is
inserted into REALCOL only when the field ID is 0; otherwise NULL is
inserted for the column CF_FOO.

The tables constructed, here's what I see:

mysql> select count(*) from realcol where instr(cf_foo, 'meta');
+----------+
| count(*) |
+----------+
|       20 |
+----------+
1 row in set (0.40 sec)

mysql> select count(*) from distcol where field_id = 0 and instr(value, 'meta');+----------+
| count(*) |
+----------+
|       20 |
+----------+
1 row in set (0.03 sec)

So the distributed-tables solution is clearly much faster.


--Sean
-------------- next part --------------
A non-text attachment was scrubbed...
Name: construct-tables.pl
Type: application/x-perl
Size: 1380 bytes
Desc: not available
URL: <http://lists.bugzilla.org/pipermail/developers/attachments/20041219/b6eef18e/attachment.pl>