Re: Normalization question

[Michael Stassen <suppressed>]

Rod Heyd wrote:
> Hi Everyone,
>
> I've got a little debate that maybe you can all help me settle.
>
> I have three tables that I need to join, but there are performance problems
> with the joins due to some misunderstandings of my predecessor about what's
> needed to join the tables efficiently.
>
> Here's the setup.
>
> t1 has a unique key defined on it, call it command_id, t1 has a 1 to many
> relationship with t2.
> t2 has t1's command_id as a foreign key constraint, plus a set of one or
> more instrument_id's.
> Each command results in one or more instruments taking data.
>
> The commanding elements defined here are then set to our instrument to aquire
> the data. When the data comes back it is organized into t3 by command_id and 
> instrument_id. So the primary key on t3 looks like this:
> command_id_instrument_id.

Yuck!

> So, now I need to write a query that looks at what was commanded in t1 and t2
> and then look for any missing data that has not yet been received.
>
> So, I've got a query that looks something like this:
>
> SELECT  stuff
> FROM t1 JOIN t2
> ON t1.command_id = t2.command_id
> LEFT JOIN t3
> ON t3.data_id = concat(t1.command_id,'_',t2.instrument_id)
>
> Now, I think everyone is going to see immediately that the join on t3 is
> going to have absolutely horrible performance,
> the join condition here can't take advantage of any indexes since the string
> function pretty much destroys any hope of that. To make matters worse, the
> left join is a total killer.

I think the LEFT JOIN isn't that big a deal, if the proper index is there and 
usable.  You can easily find out by comparing the speed of your query below 
against the speed of the same query without "LEFT".

> So my suggestion to solve the performance bottleneck is to add two columns to
> t3, command_id and instrument_id, and create a combined index on the
> command_id and instrument_id columns.

Yes, exactly.  The combined index should be UNIQUE.

> the join condition on t3 then becomes:
>
> LEFT JOIN t3
> ON t3.command_id = t1.command_id AND t3.instrument_id = t2.instrument_id
>
> This works beautifully!  The performance of this new join condition is about
> 480 times faster than the original.

Yes, this is the way this should be done.

> Here's the rub. Since there is a unique data_id that already exists which 
> combines the information in both command_id and instrument_id keys, I'm being
> told by our seasoned software developer that I am violating "classic"
> database design rules against redundant data.

It is true that you now have redundant data.  Clearly, you do not need both the 
two new columns, command_id and instrument_id, and the old column, 
command_id_instrument_id.  Redundant data is a violation of "classic" database 
design rules, so one or the other has to go if you want to follow the rules.

Which should go?  Well, the old column, command_id_instrument_id, combines the 
answer to two questions, "Which command?" and "Which instrument?", into one 
column.  That is also also a violation of the "classic" database design rules, 
and a really bad idea.  It leads to precisely the sort of problem you are trying 
to fix.  It also makes it difficult to find the rows in t3 which belong to a 
particular command, or to a particular instrument.  Those queries require string 
matching, and the latter could not use an index.  If you want to follow the 
rules, drop the old column.

> In my opinion, this is a minor "violation" at best.  We have a good reason
> for wanting to identify each data segment with our originally defined
> data_id, but this is not strictly a requirement on the database, it's more
> of an agreed upon convention that we are going to use to identify the data
> segments and distribute them to our consumers.  From a database stand point,
> the only requirement is that the data_id be unique.  It could be anything as
> far as the database is concerned, as long as the data_id remains unique, it
> doesn't matter that it may be overloaded with some "duplicate" information.
> Any more experienced DBA's than I have an opinion on this?

The strictly correct solution is to replace the old, broken column with your two 
new columns.  You can either make the combined index on the two new columns the 
PRIMARY KEY, or you can make an AUTO_INCREMENT primary key and define the 
combination of the two columns as UNIQUE.  It is then trivial to

  SELECT CONCAT(command_id, '_', instrument_id) ...

when you want to display the t3 id using the agreed upon convention.

In short, your seasoned software developer is right to want to follow the rules, 
but the rules dictate replacing the old column with the two new columns.

> Thanks!

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/suppressed