Hi, > 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 Actually this didn't give me what was expected. It also took more than 20 mins twice . I'll rerun it again if you insist. That is the reason I uploaded the file to ftp and the schema. Also I saw that autoindexes were present for the table (primary keys). >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. We purge data once a week automatically and it is configurable. So we can't use the max(rowid) trick always. Yes it works if you don't DELETE.. I'm doing all this on CentOS 7 x64. I built sqlite myself with latest sqlite-autoconf-3080801 Regards, Navin _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
