Thanks Cory.
On Fri, Jun 25, 2010 at 9:07 AM, Cory Nelson <phro...@gmail.com> wrote:
> On Fri, Jun 25, 2010 at 6:49 AM, P Kishor <punk.k...@gmail.com> wrote:
>> Is there any gotcha, any disadvantage (query complexity, db size,
>> query speed) to using a composite PK (two columns) vs. a single
>> AUTOINCREMENT INT?
>>
>> Background: I happen to have the two columns in question in my table
>> anyway. Adding an INTEGER PRIMARY KEY would use up space I don't want
>> to use. My db is big enough to worry about space from a single field.
>
> Primary keys that are not a single integer are equivalent in storage
> and complexity to a separate additional index, and composite keys
> don't have any penalty over single-column keys.
>
> Single integer primary keys you get for free. Â They take no extra
> storage or complexity because in SQLite every table already has one
> even if you don't use it (called a "rowid"). Â Specifying one merely
> gives it a new name.
>
My question was from a more generic db perspective although I asked it
here on sqlite list. Specifically, in Pg, there is no concept of
'rowid'. Even though there is an 'oid', it can be turned off, and is
anyway recommended to not be depended upon to act as a PK. Even more
so, it ('oid' in Pg), is an INT4, so it is upper-limit bound. So, let
me rephrase my question a bit --
Are composite PKs made up of two INT columns the same in query speed,
complexity and storage as a single INT PK that is not an alias of a
system row id?
What I glean from your answer --
PKs that are not a single INT, but a composite of, say, two INT
columns, "add the equivalent of an additional index." Is that what you
are saying? An index in addition to what might be created to treat
them as a PK?
And, "composite keys don't have any penalty over single-column keys."
In other words, a composite PK made up of two INT columns is just as
quick and svelte as a single INT column PK. Is that what you are
saying?
> --
> Cory Nelson
> http://int64.org
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
