Re: [sqlite] Very slow when query max(col) on empty set?

On Friday, 9 May 2014, RSmith wrote: > > On 2014/05/08 18:16, Woody Wu wrote: > >> On 2014年5月8日 GMT+08:00PM11:59:33, RSmith wrote: >> >>> On 2014/05/08 14:13, Woody Wu wrote: >>> Do you mean that my index for this case is already the best choice?

Re: [sqlite] Very slow when query max(col) on empty set?

Woody Wu wrote: > On 2014年5月8日 GMT+08:00PM9:14:52, Clemens Ladisch wrote: >> Woody Wu wrote: >>> what's the best index in the case of (1) B is an existed one, or (2) >>> B is not exists ? >> >> That depends. Create two indexes on (b,c,a) and (c,b,a), run ANALYZE, >> and check

Re: [sqlite] Very slow when query max(col) on empty set?

Out of curiosity, why is the "a < INF" clause (or something like "a < " in the original question) there? It doesn't seem to limit the results, and even if the overhead of having it there is minimal, does the fact that no one's suggested removing it mean it's needed/useful? SELECT

Re: [sqlite] Very slow when query max(col) on empty set?

On 2014/05/08 18:16, Woody Wu wrote: On 2014年5月8日 GMT+08:00PM11:59:33, RSmith wrote: On 2014/05/08 14:13, Woody Wu wrote: Do you mean that my index for this case is already the best choice? And, as a general question, for a query in the form of select max(a) from

Re: [sqlite] Very slow when query max(col) on empty set?

On 5/8/2014 12:16 PM, Woody Wu wrote: What I was wondering is that why sqlite couldn't find out earlier that b=8 c=1 denotes an empty set? If I run something like "select * from table where b=8 and c=1", it could return very quickly. Are you sure? An index on (a, b, c) wouldn't help with

Re: [sqlite] Very slow when query max(col) on empty set?

On 2014年5月8日 GMT+08:00PM11:59:33, RSmith wrote: > >On 2014/05/08 14:13, Woody Wu wrote: >> >> Do you mean that my index for this case is already the best choice? >> >> And, as a general question, for a query in the form of >>select max(a) from table where a < InF and b=B

Re: [sqlite] Very slow when query max(col) on empty set?

On 2014年5月8日 GMT+08:00PM11:59:33, RSmith wrote: > >On 2014/05/08 14:13, Woody Wu wrote: >> >> Do you mean that my index for this case is already the best choice? >> >> And, as a general question, for a query in the form of >>select max(a) from table where a < InF and b=B

Re: [sqlite] Very slow when query max(col) on empty set?

On 2014/05/08 14:13, Woody Wu wrote: Do you mean that my index for this case is already the best choice? And, as a general question, for a query in the form of select max(a) from table where a < InF and b=B and c=C. what's the best index in the case of (1) B is an existed one, or (2) B is

Re: [sqlite] Very slow when query max(col) on empty set?

On 2014年5月8日 GMT+08:00PM9:14:52, Clemens Ladisch wrote: >Woody Wu wrote: >> On 2014年5月8日 GMT+08:00AM1:51:43, Clemens Ladisch >wrote: >>> Simon Slavin wrote: You can let SQLite make a better estimate by using the 'ANALYZE' command. >>> >>> Yes.

Re: [sqlite] Very slow when query max(col) on empty set?

On 8 May 2014, at 2:14pm, Clemens Ladisch wrote: > Woody Wu wrote: >> And, as a general question, for a query in the form of >> select max(a) from table where a < InF and b=B and c=C. >> what's the best index in the case of (1) B is an existed one, or (2) B is >> not

Re: [sqlite] Very slow when query max(col) on empty set?

Woody Wu wrote: > On 2014年5月8日 GMT+08:00AM1:51:43, Clemens Ladisch wrote: >> Simon Slavin wrote: >>> You can let SQLite make a better estimate by using the 'ANALYZE' >>> command. >> >> Yes. However, even if a better estimate were to show that this index is >> not very useful,

Re: [sqlite] Very slow when query max(col) on empty set?

On 2014年5月8日 GMT+08:00AM1:51:43, Clemens Ladisch wrote: >Simon Slavin wrote: >> On 7 May 2014, at 1:29pm, Woody Wu wrote: >>> The 'explain query plan' gives same result for the first and the >second query: >>> >>> 0|0|0|SEARCH TAB mp USING COVERING INDEX

Re: [sqlite] Very slow when query max(col) on empty set?

Simon Slavin wrote: > On 7 May 2014, at 1:29pm, Woody Wu wrote: >> The 'explain query plan' gives same result for the first and the second >> query: >> >> 0|0|0|SEARCH TAB mp USING COVERING INDEX sqlite_auto_index_mp_1 (ntimereq> (~1 rows) >> >> BTW: I dont understand what

Re: [sqlite] Very slow when query max(col) on empty set?

On 7 May 2014, at 1:29pm, Woody Wu wrote: > The 'explain query plan' gives same result for the first and the second query: > > 0|0|0|SEARCH TAB mp USING COVERING INDEX sqlite_auto_index_mp_1 (ntimereq (~1 rows) > > BTW: I dont understand what the (~1 rows) mean.

Re: [sqlite] Very slow when query max(col) on empty set?

On 2014年5月7日 GMT+08:00AM3:16:35, Clemens Ladisch wrote: >Woody Wu wrote: >> The following query statement executed very slow, it took 15 secs on >my ARM device, >> >> 1. select max(time) from mytable where time < and id1 = k1 >and id2 = n. >> >> However, if I replace

Re: [sqlite] Very slow when query max(col) on empty set?

On 5/6/2014 10:19 AM, Woody Wu wrote: I observed a strange behavior. I was operating on a big table, there are 200,000 records in it. The table has a primary key or unique index, (time, id1, id2), all of these indexed columns are integers. The following query statement executed very slow, it

Re: [sqlite] Very slow when query max(col) on empty set?

Woody Wu wrote: > The following query statement executed very slow, it took 15 secs on my ARM > device, > > 1. select max(time) from mytable where time < and id1 = k1 and id2 = > n. > > However, if I replace k1with another value that can be found in the table and > keep everything

[sqlite] Very slow when query max(col) on empty set?

I observed a strange behavior. I was operating on a big table, there are 200,000 records in it. The table has a primary key or unique index, (time, id1, id2), all of these indexed columns are integers. The following query statement executed very slow, it took 15 secs on my ARM device, 1.