Thanks for clearing that up Greg ----- Original Message ----- From: D. Richard Hipp To: [EMAIL PROTECTED] Sent: Tuesday, February 10, 2004 11:52 AM Subject: Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index
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. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
