http://qs321.pair.com?node_id=129576


in reply to Migrating a 1NF table to multiple 2NF tables

To simplify your joins you may want to consider using the WHERE a.pk = b.fk syntax. It's often much clearer than the oft convoluted JOIN ... ON.
-- I took the liberty of using table aliases as well SELECT a.name, b.title, s.title, b.year FROM artist a, album b, song s WHERE a.id = b.artist_id AND b.id = s.album_id ORDER BY a.name, b.year, b.title, s.title LIMIT 2;

In some databases this can lead to much faster queries as well. In these cases, by not explicitly stating the join order, the query optimiser can step in and decide based on indices and other factors.

Another thing of note is that as of 3.23.44 MySQL does support foreign key relation(finally!) a bit in the InnoDB table type. If you are using MySQL as a relational database I urge, no implore, you to update and use said table types. Without such contraints (and fully functional transaction, but we'll ignore that for now) you're really not even using a relational database. As an added incentive they'll vastly improve the robustness of your application and remove a lot of (what should be) uneccassy work at the application level.

(And yes I do froth at the mouth a bit when expounding on the evils of bad, or the lack of, data-integrity checks ;)

To create an InnoDB table in MySQL simply append 'TYPE=INNODB' to all your CREATE TABLE statements.

CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
See here for more details on the syntax and restrictions.

Replies are listed 'Best First'.
Re: Re: !--info--Migrating a 1NF table to multiple 2NF tables
by extremely (Priest) on Dec 07, 2001 at 09:51 UTC
    While table aliases are good for people's sanity, I have to object to your suggestion not to use JOIN.

    Lazy or Implied JOINs are a bad habit. If someone is comfortable with it you shouldn't chide them. Not only is good to know the proper way once you get deep enough to want RIGHT OUTER JOINs and such but the syntax will rescue you from the dreaded "dot product bomb".

    See, without a join constraint, SQLs only choice is to join the first row of one table to every row in the next, and then the second row etc etc. A three table join of 10,000 records in each table, each a tiny 100 bytes will return at least 100,000,000,000,000 bytes of data plus framing and protocol and such.

    People rarely forget the ON xxx=yyy clause off the end of the JOIN but often when tinkering or generating code you wind up with an incomplete WHERE. I've personally seen a perl script kill a $200,000 SGI box with 1.5GB of total memory. Just shut it down, ate every drop of memory and was still trying when we finally got a root terminal to except "killall perl<RET>" at the blazing speed of 1 char every 5 seconds. =)

    When speed of data return matters, and the optimizer fails you, at least you'll be able to reorder the JOINs without refactoring the whole deal. And about 90% of the time, the natural order you follow will be the right thing.

    As to the MySQL notes on keys and the need for constraints I say a hearty Amen Brother!

    --
    $you = new YOU;
    honk() if $you->love(perl)