Rael Bauer schrieb: > 1) Is the efficiency (in terms of speed) of an index equal whether field is > INT/REAL/TEXT? (Where all data is of storage type INTEGER for INT field, REAL > for REAL, TEXT for TEXT) > > I did some tests which showed that speed is equal. My real question is: > > 2) If an indexed field contains records with a mix of storage classes (e.g. > some records are stored as REAL, some as TEXT), does this decrease speed > performance of the index in any way? > > Thanks > Rael >
If you have small tables the speed difference won't be large. But if your tables grow in size (number of rowsets) you'll measure a clear difference. The longer a index in bytes gets the less keys will fit on a single BTREE page so the depth of your BTREE where index relations are stored will get higher and you'll need more page accesses. As long as you don't get in the reagion of a depth higher than 2 levels (small size of rowsets) you won't measure a difference. But afterwards you'll see that Text > Reel > Int means also that the index access will reflect this relation. When you have indexes with more than one field the BTREE can store less keys on pages so the searchtime will increase. For sqlite there is one more special thing to take into account. If you can use an int also as a primary key then do it because if not you'll always have a hidden column rowid (int) for each rowset while when you use your own unique integer as a primary key you will have one index table less and the B+Tree will use your integer. Less space, faster access without losing anything. Hope this simple description will help. Ibrahim _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
