On 6 Jan 2018, at 3:32am, Nick <haveagoodtime2...@gmail.com> wrote:

> 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.

You’re overthinking it.  Establish your tables and indexes according to however 
the data works in your head.  Insert some plausible data into the tables. The 
more this data is like a fully-populated production database, the better.  Then 
run the SQL command "ANALYZE".

See if the results are fast enough for your intended purposes.  They probably 
will be.  Only if they’re not, start worrying about optimization.

Try to make SQL serve the way you want to organise your data, not the other way 

> 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.

More complicated than that.  For instance, once SQLite has found the right 
entry in the index it might need to look up that entry in the table to retrieve 
values which are not in the index.  So it might be better to make an index 
which contains all those values (called a "covering index").  But it might not, 
because that will make the index bigger, and will mean that your computer has 
to do a lot more disk access while searching the index.

> And does sqlite have a profile tool to get call tree or execution time of
> each functions? All I know is VDBE_PROFILE.

No.  But the shell tool has a timer which can closely time the execution of any 
command.  And that is a far more reliable way of knowing what will take longer 
or shorter in the real world than timing individual calls.

sqlite-users mailing list

Reply via email to