On 1/24/15, Keith Medcalf <[email protected]> wrote: > > You are using a WITHOUT ROWID table. Any particular reason why?
I suggested to him that it might be faster. Apparently I was wrong. :-\ >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: [email protected] [mailto:sqlite-users- >>[email protected]] On Behalf Of Parakkal, Navin S (Software Engineer) >>Sent: Friday, 23 January, 2015 11:05 >>To: General Discussion of SQLite Database; [email protected] >>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 >>[email protected] >>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
