On 23 Jan 2015, at 3:16pm, Parakkal, Navin S (Software Engineer)
<[email protected]> wrote:
> I also did another experiment. I created this table and did a vaccum and then
> the select count(*) in sqlite3 was around 2 mins.
>
> When I create an index manually after the table is loaded (imported from
> csv), select count(*) in sqlite3 was within 30 to 40 secs.
In the second case, to calculate count(*) SQLite was able to use the index you
had created. Since this index was smaller than the table, SQLite was able to
count the entries in it faster. The result would have been the same if you had
done whenever the index had been created
CREATE TABLE
.import
CREATE INDEX
time the 'select count(*) from hp_table1' command here
should yield pretty-much the same result as
CREATE TABLE
CREATE INDEX
.import
time the 'select count(*) from hp_table1' command here
If you are using a table for which rows are INSERTed but never DELETEd, then
you will get the same result almost instantly using
select max(rowid) from hp_table1
instead of counting the rows.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
