That's a fair statement. Indices not only increase the size of the data, they also slow down db writes, because the index tables need to be written as well as the data tables.
Still, you want to avoid a full table scan on a table with millions of records. Sqlite also has an explain command, just Google 'sqlite explain select.' As we think about this, I am becoming persuaded that the best approach would be to try the queries from a command-line client, no matter which db engine is used. I also guess I'm assuming that the db is at least partly responsible for the bottleneck. On Jul 23, 11:34 am, Vineet <[email protected]> wrote: > Pl. beware of indexing. > It is a double-edged sword. > Indexing too many columns would increase the data-size. > One can first run a query with "explain extended" clause (in MySQL > database). > That can tell which column(s) would need an index. > > On Jul 23, 6:29 pm, Cliff <[email protected]> wrote: > > > > > > > > > You have exposed two relatively advanced programming topics: code > > profiling and database performance tuning. > > > Because I am a relative noobie to both Python and Sqlite, I cannot > > unfortunately give you specific directions. > > > But I can offer an approach you might try. Maybe you should first > > learn where the bottleneck lies through code profiling. Generally a > > code profiler will trace the code as it runs and timestamp the steps. > > It should be relatively easy to spot long waits after database calls, > > for example. > > > Python does have built in code profiling as described > > here:http://docs.python.org/library/profile.html > > > As a short cut, you might try indexing your table(s). In general, you > > want an index on any column that appears in a where clause or an order > > by clause. > > > Is the data normalized at all, or is it all in one huge table like a > > spreadsheet would produce? If the data is not normalized, you will > > need to find a way to normalize it.
