Re: using constants

On Thu, 29 Nov 2007 23:19:08 +0100, Marten Lehmann <lehmannmapson_at_cnm.de> wrote:

>Hello,
>
>> To your first question, as others have said, it is not likely to make
>> a difference. If the word defines a well known state, and the name
>> will never change, it is just fine. A lookup table may be nice, but
>> that depends on the way it is used.
>
>would you say the same is true for joins? What would be faster,
>
>this one?
>
>domains
>-------
>name (pk)
>...
>
>records
>-------
>id (pk)
>name (fk)
>...
>
>for a query like
>
>select * from domains d, records r where d.name = r.name and d.name =
>'whatever'
>
>or this one:
>
>domains
>-------
>id (pk)
>name (unique)
>...
>
>records
>-------
>id (pk)
>domain_id (fk)
>...
>
>for a query like
>
>select * from domains d, records r where d.id = r.domain_id and d.name =
>'whatever'
>
>Mathematically it should be faster to compare two 4 byte non-zero
>integer values than two at least 8 byte strings. But the first solution
>is more elegant and intuitive.

If you are asking what would be faster in a "real life" situation, as others have pointed out, it is unlikely to make a noticeable difference (that you would care about). If you are speaking academically, perhaps it is faster, but i simply do not know enough to answer that question.

As for your queries, as i am sure you know, the first query does a redundant join, unless you are checking for existence of the record in the domain TABLE. Though, perhaps an EXISTS would be clearer and faster in that case.

Personally, i like the second case better. Because names change (or split), ids do not. And anything that can change, should not be used as the PK.

B.

>
>Regards
>Marten
Received on Fri Nov 30 2007 - 08:24:36 CST