Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??
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.

In reply to Re: !--info--Migrating a 1NF table to multiple 2NF tables by Arguile
in thread Migrating a 1NF table to multiple 2NF tables by jeffa

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others admiring the Monastery: (5)
As of 2024-03-28 20:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found