Обсуждение: primary keys as TEXT

Hi.

There can be performancs problems in having primary keys of type TEXT?
What about having a primary key of 3 columns (all of type TEXT)?


Regards  Manlio Perillo

On Jul 28, 2006, at 17:37 , Manlio Perillo wrote:

> There can be performancs problems in having primary keys of type TEXT?
> What about having a primary key of 3 columns (all of type TEXT)?

What defines a problem in terms of performance is heavily dependent  
on your particular needs and requirements. What are your  
requirements? What profiling have you done to see where your  
performance bottlenecks may be?

Michael Glaesemann
grzm seespotcode net

On Fri, 2006-07-28 at 03:37, Manlio Perillo wrote:
> Hi.
> 
> There can be performancs problems in having primary keys of type TEXT?
> What about having a primary key of 3 columns (all of type TEXT)?

The biggest problem with using text as a primary key or foreign key is
that text types are locale dependent, so that you might get one
behaviour on one server and another behaviour on another, depending on
configuration of the locale and the locale support on that machine.

For instance, if you have a locale that says that E and e are
equivalent, and another locale that says they aren't...

Michael Glaesemann ha scritto:
> 
> On Jul 28, 2006, at 17:37 , Manlio Perillo wrote:
> 
>> There can be performancs problems in having primary keys of type TEXT?
>> What about having a primary key of 3 columns (all of type TEXT)?
> 
> What defines a problem in terms of performance is heavily dependent on
> your particular needs and requirements. What are your requirements? What
> profiling have you done to see where your performance bottlenecks may be?
> 

I still don't have done profiling.

Simply in the first version of my schema I used serial keys but the
result is ugly and it force me to do a lot of joins.




Thanks and regards  Manlio Perillo

Aaron Bono ha scritto:
> On 7/28/06, *Manlio Perillo* <manlio_perillo@libero.it
> <mailto:manlio_perillo@libero.it>> wrote:
> 
>     Michael Glaesemann ha scritto:
>     >
>     > On Jul 28, 2006, at 17:37 , Manlio Perillo wrote:
>     >
>     >> There can be performancs problems in having primary keys of type
>     TEXT?
>     >> What about having a primary key of 3 columns (all of type TEXT)?
>     >
>     > What defines a problem in terms of performance is heavily dependent on
>     > your particular needs and requirements. What are your
>     requirements? What
>     > profiling have you done to see where your performance bottlenecks
>     may be?
>     >
> 
>     I still don't have done profiling.
> 
>     Simply in the first version of my schema I used serial keys but the
>     result is ugly and it force me to do a lot of joins.
> 
> 
>  
> Ugly?  Not sure what you mean by that.
> 

Because serial ids are only surrogate keys.
My tables have well definited primary keys, the only problem is that
they are of type TEXT (and spawn up to 3 columns).

My concern is: how bad can be performance?

> I do understand the problem with so many joins.  I use views so that the
> joins are only delt with once (in the database) and then all my
> applications run off the views.  That way, the applications use very
> simple queries.  The views also allow me to change the table structure
> (column names, more table normalization, etc.) without having to make
> changes to the application.
> 

View are a good idea, thanks.


Regards  Manlio Perillo