Re: [sqlite] Functions and index

[hilaner]

On 2011-06-27 17:34 Simon Davies  wrote:

> select julianday( ( select max( day_date ) from days ) );

Of course I tried this, but with a single bracket I got a syntax error. 
With double bracket it works.

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

Re: [sqlite] Functions and index

[Simon Davies]

On 27 June 2011 16:16, hilaner  wrote:
> I have hundred thousands of records in this table:
>
> CREATE TABLE days (
>        day_id INTEGER NOT NULL PRIMARY KEY,
>        day_date DATE
> );
>
> CREATE INDEX day_i ON days (day_date ASC);
>
> And then if I run such query:
>
> EXPLAIN QUERY PLAN
> SELECT JULIANDAY(MAX(day_date)) FROM days;
> 0|0|0|SCAN TABLE days (~100 rows)
>
> it will do full table scan instead of use an index.
>
> But:
>
> EXPLAIN QUERY PLAN
> SELECT MAX(day_date) FROM days;
> 0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows)
>
> so it is as I expected.
>
> Is there any other way to rewrite such kind of queries with functions?
> The query below uses an index but it is much longer:
>
> EXPLAIN QUERY PLAN
> SELECT JULIANDAY(day_date) FROM days
>   WHERE day_date IN (SELECT MAX(day_date) FROM days);
> 0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (day_date=?) (~250 rows)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 1|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows)

select julianday( ( select max( day_date ) from days ) );

>
> Best regards,
> Adam

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

[sqlite] Functions and index

[hilaner]

I have hundred thousands of records in this table:

CREATE TABLE days (
day_id INTEGER NOT NULL PRIMARY KEY,
day_date DATE
);

CREATE INDEX day_i ON days (day_date ASC);

And then if I run such query:

EXPLAIN QUERY PLAN
SELECT JULIANDAY(MAX(day_date)) FROM days;
0|0|0|SCAN TABLE days (~100 rows)

it will do full table scan instead of use an index.

But:

EXPLAIN QUERY PLAN
SELECT MAX(day_date) FROM days;
0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows)

so it is as I expected.

Is there any other way to rewrite such kind of queries with functions?
The query below uses an index but it is much longer:

EXPLAIN QUERY PLAN
SELECT JULIANDAY(day_date) FROM days
   WHERE day_date IN (SELECT MAX(day_date) FROM days);
0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (day_date=?) (~250 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows)

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