<[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 --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
