Am 10.02.2010 18:19, schrieb Alberto Simões: > Supose a table with a key that is a string (say, words from 1 to 10 > characters) or a table with a key of integers. > > How different is the efficiency on fetching one record on these tables? > > If you look into the documentation for "create table" you'll find the right answer :
An "INTEGER PRIMARY KEY" is at least twice as fast as another type of PRIMARY KEY, the reason is based on the implementation of the engine. An integer primary key substitutes the rowid column of a table. While another type of primary key always results in a second reference table (key ==> rowid) the integer primary key doesn't need a second index table. This results in a performance gain because the engine hasn't to look up the rowid in the index table to find the correct rowsets assigned to a primary key but looks directly in the data table. If you can't won't use a integer primary key as described in the documentation the performance difference between strings and numbers won't be a big deal not as long as your strings have a lenght between 1 and 10 characters cause the greatest part of the time needed to look up a key ==> rowid pair is spent with io operations to read the fileblocks. When your strings get longer you'll find, that string keys will get slower because the number of key ==> rowid pairs fitting in a btree decreases with the length of the entries. This will only matter if you get more levels of pages counted from the root page. As long as you result in the same height of btrees you won't measure a big performance loss due to the use of strings as primary keys when you don't use the "integer primary key" feature. The argument with Timing of assembler instructions would only matter if you use memory databases but even then you'll end up with "virtual memory paging" or "caching". If there are no reasons to avoid "INTEGER PRIMARY KEY" you'll have a real performance gain by using this feature !!! _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
