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

[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

Re: [sqlite] SQLite version 3.6.21

[hilaner]

 Original Message  
Subject: Re: [sqlite] SQLite version 3.6.21
From: Andreas Schwab 
To: sqlite-users@sqlite.org
Date: 2009-12-08 02:41

> $ ./sqlite3 :memory: 'create table test(integer)'
> Segmentation fault

We have the same problem and with this posted patch it works.

source: sqlite-amalgamation-3.6.21.tar.gz
compiler: arm-linux-gnueabi-gcc (GCC) 4.4.2
parameters: default (no change, only needed for cross compilation)

Regards
Adam


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

[sqlite] SQL functions - documentation?

[hilaner]

Hi All,

Is there any description of SQL functions which are implemented in SQLite?
I mean functions like substr, mean, etc. (date and time functions have their
documentation in wiki)
Only in some source files of the SQLite?

Regards,
Adam

Re: [sqlite] LIMIT does not speed up query execution

[hilaner]

Christian Smith wrote:

> Query (2) has an extra condition in the WHERE clause, thus reducing
> the result set size to be sorted. As sorting is probably an
> O(n.log(n)) operation, halving the result set will more than halve
> the time taken to sort, for example. Add that extra condition to
> query (1), and you should get similar results for both queries.

So, If I understood well, sorting is the most time consumpting part of a
query.
LIMIT is applied after all results are gathered (filtered, sorted, and so
on), so getting e.g. 20 sorted records from 20 000 item table takes much
more time than another query 20 sorted records but limited by another WHERE
condition - because sorting is done on much smaller group of records in
second case.

And - if I have to have sorted results as a small (but ordered!) part of big
amount of data - there is no way to make it faster...

Ok, thanks to all for your explanations!
Regards
Adam

[sqlite] LIMIT does not speed up query execution

[hilaner]

Hi all!

Since my database growed to more than 20 000 records, I have noticed that
select limited to a few numer of records by LIMIT takes much more time than
select limited to similar number of records by another WHERE condition.
I use sqlite_get_table function.

In my case I have the following queries:
(1) SELECT *, col_1 - col_2 WHERE col_3 = 0 AND col_4 > 0 ORDER BY col_0 ASC
col_5 DESC LIMIT 40 OFFSET 0;
(2) SELECT *, col_1 - col_2 WHERE col_3 = 0 AND col_4 > 0 AND col_6 = 5
ORDER BY col_0 ASC col_5 DESC;

And the (2) query executes much faster than (1), even it has another
contition in WHERE section. (1) takes nearly the same time like (1) without
LIMIT condition (full table select).
Is it normal?
What can I do to speed up (1) query?

(SQLite 2.8.15, one table of 16 columns and more than 2 records, Windows
2000, VC++ 6.0)

Regards
Adam