How to speed up data access?

Question

I have a sqlite database with around 22 million records. I have indexes on the right columns( on the "where" columns) When I do a query (which returns around 80 000 rows) using sqlitestudio (a sqlite editor) the query takes around a second to run. But I suspect that this is due to some form of pagination.

However.. when I run the same query in C# using system.sqlite.data, it take a long time to iterate through that 80 000 records.

Is there any way to speed this up? Should I perhaps write a wrapper on the sqlite.dll itself?

I checked the execution plan and its the same in sqlitestudio and in the c# code. Similar issue

While guessed answers are appreciated and can provide an insight (and sometimes an answer) I would appreciate concrete answers (i.e. ones you know that actually works)

Heres the code...

using (var command = new SQLiteCommand(conn))
{
    command.CommandText = "select cell, lat, lon from cell_towers where mcc = @mcc and net = @net ";
    command.Parameters.AddWithValue("@mcc", MCC);
    command.Parameters.AddWithValue("@net", MNC);
    using (SQLiteDataReader rdr = command.ExecuteReader())
    {
        // In here it takes around 1-2 minutes to loop through. Even with no code inside the loop
        while (rdr.Read())
        {

        }
    }
}   

Edit 1:

I've run the same query using sqlite3.exe (the command line program one can download from sqlite.org) and its ouput is going to the console window. Its taking a long time to loop through all the records and print it to the command window.. but remember ... its PRINTING to the COMMAND WINDOW....

Answer 1

Just indexing each column is not always useful. A lookup on two columns would require a single index on both columns:

CREATE INDEX cell_towers_mcc_net ON cell_towers(mcc, net);

A covering index for this query might be even faster, by a little bit:

CREATE INDEX cell_towers_mcc_net_cov ON cell_towers(mcc, net, cell, lat, lon);

but probably needs more storage than it's worth.

If the mcc/net columns are the primary key, consider using a clustered index instead.