Am 10.02.2010 23:17, schrieb Simon Slavin: > But that's true only if you're running a SELECT which actually uses > that column and only that column to do the searching. Which is why I > asked that question earlier on in this thread. > Simon. > The implementation of sqlite uses a B+Tree for the stored data and a BTree for primary keys, indexes etc. If you create a table with a "INTEGER PRIMARY KEY" as "CREATE TABLE" states you will end up wit only one table where the "INTEGER PRIMARY KEY" is the reference for your B+Tree where your Data resists.
If you search for more columns than the primary key value you'll get a faster query because the engine won't need to look up references from your defined primary key to the rowid in a second Btree table but use the primary key directly as a reference to the B+Tree. The performance gain is not restricted only for select but also for update, delete, insert. You can measure this easily with a table where you define a primary key with "int primary key" (results in at least one b+tree and one btree) and the same table with "integer primary key" (results in one b+tree). The op asked for a performance difference using integer keys and string keys and for sqlite a "integer primary key" is the recommended solution due to implementation and documentation. It's clear that you'll loose this performance gain when you don't use the primary key to access your data but that's not what the op asked for as far as i understood his question. Ibrahim. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
