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
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
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.
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Nick
>Sent: Friday, 5 January, 2018 20:32
>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
>'0x0A' if the length of the content of index key 'c' is always 20-25
>as I notice the format of index inside sqlite consist of the index
>I can establish mapping relation between column 'c' and a new INTEGER
>'d'. Then I am wondering if it is reasonable to create new index
>get a better performance, as sqlite stores INTEGER in a variable-
>which means there will be less index pages.
>So if it is correct that the performance of indexed select is up to
>number of index pages which is fetched in getPageNormal() within the
>I think it has positive correlation but I do not know if it is the
>And does sqlite have a profile tool to get call tree or execution
>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 mailing list