Re: [sqlite] fastest way to get the min/max

[Dan Kennedy]

On Jan 21, 2010, at 1:54 AM, Pavel Ivanov wrote:

>> Why the difference in search time between searching individually and
>> searching together?
>
> Apparently SQLite is not smart enough to optimize the search for both
> min and max to make double entrance to the index - first from the
> beginning, then from the end. It does search through the full index
> instead which is not much better than search through the full table
> (as it does without the index). But when searched separately SQLite
> understands that it can pick up just first or just last entry from the
> index.

Exactly right.

   http://www.sqlite.org/optoverview.html#minmax

>
>
> Pavel
>
> On Wed, Jan 20, 2010 at 1:47 PM, Robert Citek  
>  wrote:
>> On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov   
>> wrote:
>>> I thought at the first moment that Pavel's suggestion on using  
>>> extra index
>>> is very "expensive" in terms of megabytes on the disk, but despite  
>>> this
>>> drawback it's actually more robust.
>>
>> For my own curiosity I created a table with random text data and
>> discovered there was only a negligible difference between using
>> indexes and not when search for min and max in the same query.
>> However, when searching for min or max in separate queries the answer
>> was almost instantaneous. (details below).
>>
>> Why the difference in search time between searching individually and
>> searching together?
>>
>> Regards,
>> - Robert
>>
>> -
>>
>> $ sqlite3 sample.db 'drop table if exists sample ;
>> create table sample (foo text) ; '
>>
>> $ jot -r -c 8000 a z |
>> tr -d '\n' |
>> fold -w 8 |
>> fmt -w 8 |
>> sqlite3 sample.db '.imp "/dev/stdin" "sample"'
>>
>> $ sqlite3 sample.db 'select count(*) from sample ; '
>> 1000
>>
>> $ time -p sqlite3 sample.db 'select min(foo), max(foo) from  
>> sample ; '
>> aaaq|ytyd
>> real 3.24
>> user 3.10
>> sys 0.11
>>
>> $ time -p sqlite3 sample.db 'create index sample_foo on sample  
>> (foo) ; '
>> real 838.92
>> user 53.68
>> sys 38.46
>>
>> $ time -p sqlite3 sample.db 'select min(foo), max(foo) from  
>> sample ; '
>> aaaq|ytyd
>> real 3.19
>> user 3.13
>> sys 0.06
>>
>> $ time -p sqlite3 sample.db 'select min(foo) from sample ; '
>> aaaq
>> real 0.00
>> user 0.00
>> sys 0.00
>>
>> $ time -p sqlite3 sample.db 'select max(foo) from sample ; '
>> ytyd
>> real 0.00
>> user 0.00
>> sys 0.01
>>
>> $ sqlite3 --version
>> 3.6.10
>> ___
>> 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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fastest way to get the min/max

[Robert Citek]

On Wed, Jan 20, 2010 at 1:54 PM, Pavel Ivanov  wrote:
>> Why the difference in search time between searching individually and
>> searching together?
>
> Apparently SQLite is not smart enough to optimize the search for both
> min and max to make double entrance to the index - first from the
> beginning, then from the end. It does search through the full index
> instead which is not much better than search through the full table
> (as it does without the index). But when searched separately SQLite
> understands that it can pick up just first or just last entry from the
> index.

Fair enough.  Thanks. - Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fastest way to get the min/max

[Pavel Ivanov]

> Why the difference in search time between searching individually and
> searching together?

Apparently SQLite is not smart enough to optimize the search for both
min and max to make double entrance to the index - first from the
beginning, then from the end. It does search through the full index
instead which is not much better than search through the full table
(as it does without the index). But when searched separately SQLite
understands that it can pick up just first or just last entry from the
index.


Pavel

On Wed, Jan 20, 2010 at 1:47 PM, Robert Citek  wrote:
> On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov  wrote:
>> I thought at the first moment that Pavel's suggestion on using extra index
>> is very "expensive" in terms of megabytes on the disk, but despite this
>> drawback it's actually more robust.
>
> For my own curiosity I created a table with random text data and
> discovered there was only a negligible difference between using
> indexes and not when search for min and max in the same query.
> However, when searching for min or max in separate queries the answer
> was almost instantaneous. (details below).
>
> Why the difference in search time between searching individually and
> searching together?
>
> Regards,
> - Robert
>
> -
>
> $ sqlite3 sample.db 'drop table if exists sample ;
> create table sample (foo text) ; '
>
> $ jot -r -c 8000 a z |
> tr -d '\n' |
> fold -w 8 |
> fmt -w 8 |
> sqlite3 sample.db '.imp "/dev/stdin" "sample"'
>
> $ sqlite3 sample.db 'select count(*) from sample ; '
> 1000
>
> $ time -p sqlite3 sample.db 'select min(foo), max(foo) from sample ; '
> aaaq|ytyd
> real 3.24
> user 3.10
> sys 0.11
>
> $ time -p sqlite3 sample.db 'create index sample_foo on sample (foo) ; '
> real 838.92
> user 53.68
> sys 38.46
>
> $ time -p sqlite3 sample.db 'select min(foo), max(foo) from sample ; '
> aaaq|ytyd
> real 3.19
> user 3.13
> sys 0.06
>
> $ time -p sqlite3 sample.db 'select min(foo) from sample ; '
> aaaq
> real 0.00
> user 0.00
> sys 0.00
>
> $ time -p sqlite3 sample.db 'select max(foo) from sample ; '
> ytyd
> real 0.00
> user 0.00
> sys 0.01
>
> $ sqlite3 --version
> 3.6.10
> ___
> 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

Re: [sqlite] fastest way to get the min/max

[Robert Citek]

On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov  wrote:
> I thought at the first moment that Pavel's suggestion on using extra index
> is very "expensive" in terms of megabytes on the disk, but despite this
> drawback it's actually more robust.

For my own curiosity I created a table with random text data and
discovered there was only a negligible difference between using
indexes and not when search for min and max in the same query.
However, when searching for min or max in separate queries the answer
was almost instantaneous. (details below).

Why the difference in search time between searching individually and
searching together?

Regards,
- Robert

-

$ sqlite3 sample.db 'drop table if exists sample ;
create table sample (foo text) ; '

$ jot -r -c 8000 a z |
tr -d '\n' |
fold -w 8 |
fmt -w 8 |
sqlite3 sample.db '.imp "/dev/stdin" "sample"'

$ sqlite3 sample.db 'select count(*) from sample ; '
1000

$ time -p sqlite3 sample.db 'select min(foo), max(foo) from sample ; '
aaaq|ytyd
real 3.24
user 3.10
sys 0.11

$ time -p sqlite3 sample.db 'create index sample_foo on sample (foo) ; '
real 838.92
user 53.68
sys 38.46

$ time -p sqlite3 sample.db 'select min(foo), max(foo) from sample ; '
aaaq|ytyd
real 3.19
user 3.13
sys 0.06

$ time -p sqlite3 sample.db 'select min(foo) from sample ; '
aaaq
real 0.00
user 0.00
sys 0.00

$ time -p sqlite3 sample.db 'select max(foo) from sample ; '
ytyd
real 0.00
user 0.00
sys 0.01

$ sqlite3 --version
3.6.10
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fastest way to get the min/max

[Robert Citek]

On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov  wrote:
>> One of many ways would be to precompute the min/max into a separate
>> table and then query that table whenever you need the min/max.
>
> I thought at the first moment that Pavel's suggestion on using extra index
> is very "expensive" in terms of megabytes on the disk, but despite this
> drawback it's actually more robust.

Agreed.  While storing the min/max in a separate table may be one of
the fastest ways, it is unlikely to be the optimal way, as we do not
know all the constraints (e.g. robustness, datatype, table size, use
cases)

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fastest way to get the min/max

[Max Vlasov]

>
>
>
> One of many ways would be to precompute the min/max into a separate
> table and then query that table whenever you need the min/max.
>

Only if one have full control about how the data changes what with so
widespread format as sqlite almost always not the case. I mean without
triggers you won't be able to control the moment when someone do mass
deletes or inserts with an external admin and these are the points when
actual min/max may change. Even the author of the subject can sometimes
forget about the fact that his data has such dependency and do something
affecting this.

I thought at the first moment that Pavel's suggestion on using extra index
is very "expensive" in terms of megabytes on the disk, but despite this
drawback it's actually more robust.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fastest way to get the min/max

[Robert Citek]

> On Wed, Jan 20, 2010 at 6:24 PM, hi  wrote:
>> For my application I am storing about "1770" rows into sqlite table,
>> and when taking 'min' or 'max' it takes about ~7 to 10 seconds.
>>
>> Can you please suggest effective ways to get min/max values.

How are you currently getting the min/max values?  What have you tried so far?

One of many ways would be to precompute the min/max into a separate
table and then query that table whenever you need the min/max.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fastest way to get the min/max

[hi]

Sorry for the wrong subject:

On Wed, Jan 20, 2010 at 6:24 PM, hi  wrote:

> Hi,
>
> For my application I am storing about "1770" rows into sqlite table,
> and when taking 'min' or 'max' it takes about ~7 to 10 seconds.
>
> Can you please suggest effective ways to get min/max values.
>
> Thank you in advance.
> -Hiral
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users