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

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)

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 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

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?

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