Does this trick work on the primary key? If not, why?
> From: max.vla...@gmail.com
> Date: Wed, 23 Feb 2011 16:09:04 +0300
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
> On Tue, Feb 22, 2011 at 9:59 PM, Greg Barker <fle...@fletchowns.net> wrote:
>
> > I'm currently dealing with a similar issue. I've found that the page_size
> > PRAGMA setting can have a dramatic effect on how long it takes to "warm up"
> > the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column)
> > takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes
> > 8.5 seconds. This was done with a reboot between each test.
> >
> > This page recommends a page_size of 4096:
> > http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows
> >
> > If I see better performance with the larger page sizes (going to test 16384
> > and beyond after this) is there any reason not to use them?
> >
> >
> Greg, you should also take the record size into account. My hypothesis is
> that if your record is comparatively small (several fits into 1024) the
> speed of select count will be the same for any page size (my quick tests
> confirm this). It's interesting to know what is an average size of your
> record to understand why the numbers are so different.
>
> Returning to the original topic, for performance reasons I sometimes
> recommend using an index created on the id/rowid. It's a strange construct
> that makes no sense, but actually it sometimes give a speed improvement.
> This is because any index contains only the data used in it and if the query
> doesn't require getting additional data from the table it was created for,
> sqlite only reads this index and nothing else.
>
> So to get the fastest count result one can create the following index
> (assuming id is the alias for rowid)
>
> CREATE INDEX [idx_MyTableId] ON [MyTable] ([ID] )
>
> And use the following query
>
> SELECT COUNT(id) from (SELECT id FROM MyTable ORDER By Id)
>
> "Order by" here forces using this index and I used outer select since
> count(id) inside the main select for unknown reasons triggers the table
> scanning.
>
> For any query in my tests that usually takes 5-50 seconds, this one is
> always less than a second. But is costs a little in term of the size (the
> index takes space) and the speed of insert. If this is a small price to pay
> then this may be an answer.
>
> Max
> _______________________________________________
> 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
