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,
Charlieif 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)|||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|||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!|||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?|||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|||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|||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|||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|||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|||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|||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|||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?|||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|||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|||<voice type="mr. burns">ehhhhhhhhhhhhxcellent</voice>

can we now revisit some earlier posts, like, say, starting around post #5?

pat: you (original poster) should declare a unique index
me: wouldn't the PK do?
pat: no, not all engines create an index for the PK
me: oh? which ones don't?
pat: mysql doesn't
me: what?! surely it does...
pat: not if it has been turned off
me: what? you can do that?|||create table patp
( id integer not null primary key
, foo varchar(9)
, bar varchar(37)
)
...this does create a unique index, unless one happens to be using a nefarious web farm
DB2 for z/OS does not automatically create an index in this case.

The unique index has to be created (manually) before inserting into this table.

Only when using schema's (which were only introduced in DB2 for z/OS v8) one gets the "automatic index creation".
But even in v8, schema's need not be used.|||After some offline discussion with R937, I want to reiterate what I said earlier, with added emphasis.

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.Using most of the major database products, these indicies will be created automagically for you. What I wanted the poster to do was verify that the expected indicies actually do exist for them, for their engine in their database. We (both R937 and I) expect those indicies to exist, but I'm a "belt and suspenders" type that is willing to take an extra minute or two to ensure that what I expect really exists where the rubber meets the road.

-PatP|||thanks pat

and the fact that there are engines where you can create a primary key but it's not going to be unique, well, that just frosts my petunias...

:)|||This is an example of my favourite type of thread. In ever so many ways :)|||Very interesting material in this thread.

I'm using DB2 v8.2 on AIX by the way.