On May 3, 2004, at 10:24 AM, Andrus Adamchik wrote:
> I guess nobody cared to implement this, since having a separate join
> table
> for each n:m is arguably cleaner design and makes db data maintenance
> easier. But I'd like to hear arguments in favor of such generic join
> table
> design.
We tried this when we ran into MySql problems, and a massive join table
helped us out of a performance problem
On this project, every data attribute was in its own table, as we were
not sure which attributes a given thingie (technical term) might have.
Further, some might have one instance of a given tag, while others
might have many.
For example, an experiment might have a String attribute "Experiment
Name" and six string attributes "Experiment Technician". We hung every
one of these off of the central Experiment node via a StringProperty
table and a "Has A", "Is A", "Is A Kind Of", etc., join table.
When we ran this, MySql got bogged down in the joins, because getting
all experiments with a given author and technician required a zillion
joins through a bunch of different tables. The large number of tables
with no obvious cardinality differences blew out the query cache, and
made mysql resort to temporary disk tables. Ick.
When we turned the twenty join tables into one big join table with a
type attribute, query performance improved markedly. The query cache
got blown out a lot less often. Further, the optimizer seemed to be
making better choices in that circumstance. Frankly, that last
surprised me, as I would have expected it to do worse because this
blurred out any cardinality differences between different join types.
Frankly, I liked the different table layout a lot more, but the
performance was hard to argue with. (This WAS back in the early 3.23
days, so it may be less relevant now. If nothing else, Innodb has very
different query performance characteristics.)
Scott
This archive was generated by hypermail 2.0.0 : Mon May 03 2004 - 13:38:04 EDT