-----Original Message----- From: Bertrand Mansion [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 10, 2004 3:48 AM To: D. Richard Hipp; [EMAIL PROTECTED] Subject: Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index
<[EMAIL PROTECTED]> wrote : > Greg Obleshchuk wrote: >> >> So in these cases there is no benefit from creating an index on a column >> that is INTEGER PRIMARY KEY? >> > > Putting an index on an INTEGER PRIMARY KEY will make INSERT, > DELETE, and UPDATE slower since the index must be maintained. > But no SELECT will ever use the index. So adding an index > to an INTEGER PRIMARY KEY is less than no benefit - it hurts. > > See ticket #292. If you say "UNIQUE PRIMARY KEY" (as some > users want to do) SQLite will create two identical indices > Only one index will ever be used - the other justs wastes > CPU time and disk space. I'll get around to fixing that > someday. Probably at the same time I should rig it so that > attempts to create named indices on PRIMARY KEY are ignored > too. Once that happens, you can create indices on your > INTEGER PRIMARY KEY all you want - SQLite will ignore your > attempts - and everything will work at maximum efficiency > regardless of what you try to do. Thanks for the info, I'll have to review my performance tests to find were they are wrong then. Sorry about the confusion it created. What about the other question on how to know that there is such an internal index for the table ? PRAGMA index_list() does not return this index as it seems hidden. At the moment, I have to do a PRAGMA table_info(), find the primary key, then check if it is not present in PRAGMA index_list(). I can live with that and I guess changing index_list() would be a BC break. But I would be interested to know if there are other more intuitive ways, other than parsing the CREATE TABLE sql query. Bertrand Mansion Mamasam I'm new to SQLite, and so far am very impressed. But I guess you could say I have "been around the block" at least couple of times on the Database Wagon. My first shrink wrapped PC "database" was Paradox Release 2.0. And that was after I had been using Mainframe stuff for far too long. Maybe I've missed something, but the only time I have ever used any generated key value was to insure uniqueness thus guaranteeing a one to one relationship in a design (i.e. Insuring I can find the right detail for a given instance of "Mr. Smith.") If any database system I am using generates an internal "invisible" unique value for each row, it serves me no design purpose. I still must have a visible selectable value to insure uniqueness. Since I have no real desire to dig into the SQLite source, I have no idea why an internal key is being generated, nor do I care. As a database designer I only concern myself with the things I can see and do, without "lifting the hood". Relying on a product's unique "undocumented features" always comes back to roost. The IT world has always been moving toward "interoperability and reuse." Implementing any design by relying on a unique feature of a particular product is a very serious violation of "good" design principals, IMHO. The whole reason I am evaluating SLQite is because I am considering dumping the "bigger" less graceful database systems I have been using. Had I used unique features implemented in my designs currently running on those other database systems, I would probably not be looking to move so readily to yet another database system. I have been burned so many times by both "automatic" key generators as well as numeric key values I never ever rely on either in a design. I still do it the "old fashioned" way. I do it myself and generate a unique alpha-numeric key. Well, back to being impressed :-) I'm still looking things over. Fred --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
