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 around. > 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. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
