<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Sean McAfee wrote:
<blockquote cite="mid20041216025041.53C89BC60@mail.schwag.org"
type="cite">
<pre wrap="">Myk Melez <a class="moz-txt-link-rfc2396E"
href="mailto:myk@mozilla.org"><myk@mozilla.org></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">MySQL also provides us tools for querying and modifying the structure of
tables in the database so that we can build tools to manage the columns,
and its search and other algorithms are designed with the assumption
that databases are built using its architecture for structuring and
relating data.
</pre>
</blockquote>
<pre wrap=""><!---->
What are these tools?
</pre>
</blockquote>
<br>
They're the MySQL data definition and utility statements.<br>
<br>
<blockquote cite="mid20041216025041.53C89BC60@mail.schwag.org"
type="cite">
<pre wrap=""> </pre>
<blockquote type="cite">
<pre wrap="">So I'm loathe to replicate this architecture. I think we're better off
making custom fields be real columns in the database, just like standard
fields, and creating independent value list and mapping tables for
select and multi-select custom fields, just as we do for standard fields
of those types.
</pre>
</blockquote>
<pre wrap=""><!---->
The standard fields of those types are of the MySQL-specific ENUM type,
aren't they? I had thought that greater platform-independence was an
ongoing goal. Or has this changed in recent releases? My information may
be out of date.
</pre>
</blockquote>
Nope, you're not out of date, we are indeed replacing enums with tables
of values. I'm referring to fields like product, component, and
target_milestone as well as the fields currently using enums which are
being converted to work like the aforementioned fields.<br>
<br>
<blockquote cite="mid20041216025041.53C89BC60@mail.schwag.org"
type="cite">
<blockquote type="cite">
<pre wrap="">Besides making custom fields work like standard fields, simplifying
their integration with existing code and the migration of fields from
standard to custom (and vice versa), this approach minimizes the
side-effect risk associated with building independent field management
on top of MySQL's existing infrastructure and the development cost of
maintaining two ways of accessing and manipulating fields.
</pre>
</blockquote>
<pre wrap=""><!---->
There are significant efficiency concerns with a fields-as-columns
approach.
Suppose you have a largeish Bugzilla installation with, oh, say 100,000
bugs and 200 custom fields across twenty products. Suppose further that you
want to run a simple query against one particular field: find all bugs where
the short string field "foo_field" contains the substring "meta". In my
scheme, you run these two queries:
SELECT field_id
FROM custom_fields
WHERE field_name = 'foo_field';
Then, using the returned field_id (say, 99):
SELECT bug_id
FROM cf_shortstring
WHERE field_id = 99
AND INSTR(value, 'meta');
The first query is very fast, thanks to the index on FIELD_NAME, and would
be fast anyway with only 200 rows to check. The second one quickly targets
only those bugs in products that have foo_field as a member, thanks to the
index on FIELD_ID. (I suppose the queries could be combined into one.)
If custom fields were "real" columns, you'd do something like this:
SELECT bug_id
FROM bugs
WHERE INSTR(cf_foo_field, 'meta');
This query has to scan *every bug in the installation*; there's no way to
restrict it only to bugs that actually have a foo_field. BUGS is a huge,
sparse table, with 90% of its columns NULL in any given row, on average.
Say goodbye to casual "SELECT * FROM BUGS WHERE..." queries at the mysql
client command line, too.
</pre>
</blockquote>
Notwithstanding that we should use fulltext indexes for these kinds of
searches, this works pretty well for an existing standard
field on the production b.m.o server:<br>
<hr size="2" width="100%"><tt>mysql> select count(*) from bugs;<br>
+----------+<br>
| count(*) |<br>
+----------+<br>
| 274656 |<br>
+----------+<br>
1 row in set (0.00 sec)<br>
<br>
mysql> select count(*) from bugs where status_whiteboard != '';<br>
+----------+<br>
| count(*) |<br>
+----------+<br>
| 32931 |<br>
+----------+<br>
1 row in set (0.75 sec)<br>
<br>
mysql> select bug_id from bugs where INSTR(status_whiteboard,'meta');<br>
+--------+<br>
| bug_id |<br>
+--------+<br>
| 4553 |<br>
| 9410 |<br>
| 12309 |<br>
| 16029 |<br>
| 23583 |<br>
| 35154 |<br>
| 38761 |<br>
| 45349 |<br>
| 51279 |<br>
| 52577 |<br>
| 56812 |<br>
| 63871 |<br>
| 63872 |<br>
| 69533 |<br>
| 80613 |<br>
| 89424 |<br>
| 90669 |<br>
| 92763 |<br>
| 93969 |<br>
| 127034 |<br>
| 127147 |<br>
| 139820 |<br>
| 150783 |<br>
| 161891 |<br>
| 190406 |<br>
| 190545 |<br>
+--------+<br>
27 rows in set (0.79 sec)<br>
</tt>
<hr size="2" width="100%">(one bug ID removed from the result set above
because it's a confidential bug)<br>
<br>
<blockquote cite="mid20041216025041.53C89BC60@mail.schwag.org"
type="cite">
<pre wrap="">Speaking of NULL, you'd have to give up the capability to have NULL custom
field values, since you couldn't do this:
SELECT bug_id
FROM bugs
WHERE cf_foo_field IS NULL;
</pre>
</blockquote>
Why not?<br>
<br>
Of course, we don't have to put all of these columns into the bugs
table. We can do that, but we can also put each one into its own table
(so that bugs->custom field is a 1->0|1 relationship, and the
database uses no more storage for the custom field than necessary), all
of them into one other table, or some combination of these three,
depending on what makes the most sense.<br>
<br>
<blockquote cite="mid20041216025041.53C89BC60@mail.schwag.org"
type="cite">
<pre wrap="">My company's prior bug tracking system, TeamTrack, stored field data in huge
sparse tables, and its speed was/is atrocious. I didn't have to think very
hard in the early days to know I didn't want to go the same route.
</pre>
</blockquote>
Bugzilla is also somewhat sparse, but it's not a major performance
problem, as far as I know. In any case, relationship DB models are
designed to deal with sparseness (in fact, that's part of the reason
they were developed) by moving sparse data to a separate table, as we
do already for some standard fields.<br>
<br>
We can do that for sparse custom fields (and even sparse standard ones
still in the bugs table) with the fields-as-columns approach while
keeping non-sparse fields in the bugs table, improving performance for
those fields (because we don't have to do joins or multiple queries),
unlike with the fields-as-data approach, which always requires joins or
multiple queries.<br>
<br>
<blockquote cite="mid20041216025041.53C89BC60@mail.schwag.org"
type="cite">
<pre wrap="">As an example of another, less important efficiency issue, suppose you want
get all of a single bug's custom field data. You'd either do this:
SELECT *
FROM bugs
WHERE bug_id = 99;
This would fetch all column data, most of which would be NULL and irrelevant
in the hypothetical situation described above. The alternative would be to
construct a list of known-to-be-relevant column names, and do this:
SELECT field_1, field_2, field_3, field_4, field_5, field_6,
field_7, field_8, field_9, field_10, field_11, field_12,
field_13, field_14, field_15, field_16, field_17, field_18,
field_19, field_20
FROM bugs
WHERE bug_id = 99;
Yuck! Both solutions are inelegant, which experience has taught me is
usually an indication of bad design.
</pre>
</blockquote>
But we'd have a list of custom fields (probably generated from a simple
query or a sub-query), so the latter solution would be
more like:<br>
<br>
SELECT $list_of_fields<br>
FROM bugs<br>
WHERE bug_id = 99;<br>
<br>
... which is both simple and elegant.<br>
<br>
<blockquote cite="mid20041216025041.53C89BC60@mail.schwag.org"
type="cite">
<pre wrap="">Fields-as-columns may make life easier for the programmer, but it's the
needs of the users that should be coming first.
</pre>
</blockquote>
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.<br>
<br>
-myk<br>
<br>
</body>
</html>