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