If I understand your question correctly you have not normalized your data. The whole point of a RELATIONAL DATABASE is that the relationships are based ON THE DATA and ONLY ON THE DATA. If you have not normalized you data to at least BCNF you can expect terrible performance and all sorts of access and update anomalies.
https://en.wikipedia.org/wiki/Database_normalization Your schema, assuming that c is unique text should be something like (add the COLLATE NOCASE if it is not case sensitive): create table t3 ( d integer primary key, c text [collate nocase] unique ); create table t2 ( a INTEGER, b INTEGER, d INTEGER REFERENCES t3 ); create index t2d on t2 (d); + whatever indexes you need on a and b ... When you add a new c, you insert it into t3 and then find out what the last_rowid (d) was so you can insert a and b and d in t2. Or if the c already exists, then you lookup d and use a, b, d to insert into t2. populate it with data, and run ANALYZE; select a,b,c from t3, t2 where t2.d = t3.d and (your other conditions on a b and c) Then let the query planner decide how to compute the answer ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Nick >Sent: Friday, 5 January, 2018 20:32 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] The performance of indexed select > >I am trying to analysis the performance of indexed select. > >CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT); >CREATE INDEX t2c ON t2(c); > >I think there may be much more leaf index b-tree pages whose header >is >'0x0A' if the length of the content of index key 'c' is always 20-25 >bytes, >as I notice the format of index inside sqlite consist of the index >key and >rowid. > >I can establish mapping relation between column 'c' and a new INTEGER >column >'d'. Then I am wondering if it is reasonable to create new index >t2(d) to >get a better performance, as sqlite stores INTEGER in a variable- >length way >which means there will be less index pages. > >So if it is correct that the performance of indexed select is up to >the >number of index pages which is fetched in getPageNormal() within the >select? >I think it has positive correlation but I do not know if it is the >major >constraint. > >And does sqlite have a profile tool to get call tree or execution >time of >each functions? All I know is VDBE_PROFILE. > >Thanks for any light you can shed. > > >I want to profile sqlite > > > >-- >Sent from: http://sqlite.1065341.n5.nabble.com/ >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
