Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69

    Unanswered: Joining multiple pks

    I have 2 tables that I need to pull data from.


    Table 1
    PK ID
    PK Name
    PK Address
    PK State
    Postion
    Status

    Table2
    PK ID (FK)
    PK Name (FK)
    PK Address (FK)
    PK State (FK)
    PK Actions
    PK History


    Now i dont believe this db was designed in the most efficient way to begin with, but I'm trying to write a query to effectively pull data from it.


    SELECT DISTINCT Table1.ID, Table1.Name, Table1.Position, Table1.Status, Table2.Address, Table2.State, Table2.Actions, Table2.History
    FROM Table1, Table2
    WHERE Table1.ID=Table2.ID AND
    Table1.Name=Table2.Name AND
    Table1.Address=Table2.Address AND
    Table1.State=Table2.State AND
    Table1.ID = "123"

    This is what i got so far. Not very pretty, and the SQL is still running. Basically I need to join these 2 tables, by 4 fields. The field names have been changed bc the real ones are kind of confusing.

    I'd appreciate any help possible,

    Thanks,
    Charlie

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if your query is really slow, then most likely the FK in Table2 needs an index, but your SQL is fine -- i prefer JOIN syntax over table list syntax, but the query is fine

    well, except for the DISTINCT, you probably don't need that (and it does involve a total sort of all columns in all rows, so removing it will definitely speed up the query)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'll second R937's suggestion for a non-unique index on Table 2 (ID, Name, Address, State). That should improve performance a great deal.

    I would also recommend that you ensure that there is a unique index on Table1 (ID, Name, Address, State), and that you make sure that ID is the first (leftmost) column in that index so that the optimizer can quickly find the row by id.

    Just curious, but what database engine are you using? There are some engine specific tips that could apply, especially if you have very large tables.

    -PatP

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    On some systems it might even help to add the condition
    AND Table2.ID = '123'
    (which logically speaking is of course redundant).
    Certainly when that column has an index, this could speed up the query a lot!
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    I would also recommend that you ensure that there is a unique index on Table1 (ID, Name, Address, State) ...
    i think the PK adequately covers this requirement, no?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    i think the PK adequately covers this requirement, no?
    No. Not all database engines generate an index to enforce the PK definition, although most of them do. As a side note, a PK index might include the ID column, but not as the first column in the definition (different engines that support DRI have different rules for how they manage the PK definition).

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just so that i don't look like a complete idiot the next time someone asks me if PK uniqueness is enforced by means of a unique index, would you kindly give an example of a database engine which does not do this

    also, please be careful not to proliferate the idea of declaring a separate unique index on the PK column(s), because in most databases this will be redundant, superfluous, inefficient, and redundant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Many implementations of MySQL do nothing whatsoever with DRI. They allow you to declare it, then completely ignore that declaration because the data file they are using doesn't support it. Very few implementations that I've seen even can support FK definitions, and a significant number of commercial implementations choose not to implement PK declarations to improve insert performance and reduce their internal tech support load.

    Some versions of DB2 use "interesting" ways to determine how the PK will be enforced, for example they'll create no index for very small tables (because a table scan is cheaper than an index lookup for small amounts of data in that specific implementation), and don't have a way for the engine to change that decision if the table grows. They also tend to force integers and dates toward the end of any index unless you get very specific about it.

    That's exactly why I was asking what engine the poster was using in my first response. There can be all kinds of engine specific quirks that can cause performance problems like this, and they are decidedly NOT intuitively obvious. At least if we knew what engine they were having problems with, we might have a better chance at helping them.

    Besides all of the things you pointed out about creating a separate unique index to "back up" an existing PK, that can be overkill too.

    -PatP

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    Many implementations of MySQL do nothing whatsoever with DRI.
    so what? PK indexes <> support DRI

    i'm pretty sure MySQL uses an index to enforce PK uniqueness

    the DB2 example (no index created for the PK) is nice, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, not at all! What I'm saying is that most of the MySQL installations that I have experience using do not have any support at all for DRI.

    Out of the box, the current generation of MySQL uses MyISAM. The default install does provide an index to enforce the PK, but most of the "web farm" operators disable that to increase performance and to reduce the amount of tech support that they need to provide their users. We won't go into what I think of that practice, it would just infuriate me for no good reason! At least as far as I know, MyISAM does not provide any support for FKs no matter what you do with it.

    I guess that my point was that there are a number of ways to set up databases that use SQL or SQL-like languages, with varying degrees of support for DRI. We can't assume that just because a poster thinks that they have DRI that they've even formally declared it, and without confirming the details such as database engine, etc we can't assume that they have the features that we take for granted.

    -PatP

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    stop with the DRI already

    engine creates unique index for PK, yes or no? that's all, yes or no -- forget the DRI stuff

    that story about web farms disabling indexes to enforce the PK, i'm going to look into that, because that's insane
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How can you have a PK without DRI? If I can't declare it using standard SQL constructs, then have the database engine enforce that declaration, it is simply a pleasant notion to me. There are ways to coerce many of the database engines into doing what we expect a Relational Algebra Primary Key to do, but those fall into the category of what I consider to be "engine specific tricks", not what I consider to be a PK.

    -PatP

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aw come on pat, give it up

    how can you have a PK without DRI? like this --

    create table patp
    ( id integer not null primary key
    , foo varchar(9)
    , bar varchar(37)
    )

    voila, i have declared a primary key

    you have admitted that yes, this does create a unique index, unless one happens to be using a nefarious web farm

    is that more or less what you're saying?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    how can you have a PK without DRI? like this --

    create table patp
    ( id integer not null primary key
    , foo varchar(9)
    , bar varchar(37)
    )
    You used DRI.

    -PatP

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh yeah, if you are using Microsoft SQL, Oracle, or Sybase, then you'll create a unique index to enforce the primary key that you declared using Declared Referential Integrity.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •