You are using a WITHOUT ROWID table. Any particular reason why? Have you tried using an ordinary table? What type is your "TIME" field? Or did you mean TEXT but misspell it? Do you want the primary key columns to contain null, or is just defining things that are NOT NULL as being nullable just an oversight (or laziness)?
--- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Parakkal, Navin S (Software Engineer) >Sent: Friday, 23 January, 2015 11:05 >To: General Discussion of SQLite Database; d...@hwaci.com >Subject: Re: [sqlite] sqlite3 very slow even after creating without rowid > >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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
