[sqlite] Adding R-Tree index module to existing project

[A Gilmore]

I have a project that compiles SQLite with the following options:

  -DSQLITE_SECURE_DELETE=1
  -DSQLITE_THREADSAFE=1
  -DSQLITE_CORE=1
  -DSQLITE_ENABLE_FTS3=1
  -DSQLITE_ENABLE_UNLOCK_NOTIFY=1
  -DSQLITE_DEFAULT_PAGE_SIZE=32768
  -DSQLITE_MAX_DEFAULT_PAGE_SIZE=32768
  -DSQLITE_MAX_SCHEMA_RETRY=25

and sometimes:

  -DSQLITE_ENABLE_LOCKING_STYLE=1

The project is stable and makes heavy use of SQLite.  I'd like to enable 
the R-Tree index (-DSQLITE_ENABLE_RTREE=1), can I expect any impact to 
the project beyond allowing the use of R-Tree indexes?  Will any 
existing code work differently (gotchas, stability issues, etc)?


I don't believe this is the case (the docs make no mention of it), but 
confirmation from someone with greater knowledge of the internals would 
be appreciated.


Thank you for your time.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] R-Tree index not used when query has an ORDER BY clause

[A Gilmore]

Hello,

My query wont use the R-Tree index if I provide an ORDER BY clause and 
performance degrades considerably.  Obviously I can work around with by 
making the R-Tree query a subquery and sorting the results, but would 
like to know why this is necessary?  I've done an VACUUM and ANALYZE.


Is this expected behavior?  I haven't found any mention of it in the docs.


The schema is akin to the following:

CREATE TABLE events (
id  INTEGER PRIMARY KEY,
cal_id  INTEGER NOT NULL, -- This is a FK, ~75 possible values
begins  TIMESTAMP NOT NULL, -- unix epoch
endsTIMESTAMP NOT NULL -- unix epoch
);

INSERT INTO events VALUES (null, 123, 1356624000, 1356634800);
INSERT INTO events VALUES (null, 12452, 1356624000, 1356634800);
INSERT INTO events VALUES (null, 34653, 1356624000, 1356634800);
INSERT INTO events VALUES (null, 89287, 1356624000, 1356634800);
-- 55000 rows in events for testing, production will have 500k-1m rows

CREATE VIRTUAL TABLE events_idx USING rtree(id, begins, ends);
INSERT INTO events_idx (id,begins,ends) SELECT id,begins,ends FROM events;

-- Query A, runtime is 300ms+, 8ms without the ORDER BY
SELECT DISTINCT *
FROMevents_idx AS ei
INNER JOIN  events AS e ON e.id = ei.id
WHERE   ei.begins < 1356753600
AND ei.ends > 1356624000
AND e.cal_id IN (123,12452,567,1234,34653,4249,89287)
ORDER BYe.begins, e.ends - e.begins;

-- Query B, runtime is ~8ms
SELECT * FROM
(
  SELECT DISTINCT *
  FROMevents_idx AS ei
  INNER JOIN  events AS e ON e.id = ei.id
  WHERE   ei.begins < 1356753600
  AND ei.ends > 1356624000
  AND e.cal_id IN (123,12452,567,1234,34653,4249,89287)
) AS sub
ORDER BYsub.begins, sub.ends - sub.begins;




Thank you for your time.

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

Re: [sqlite] Create table if not exists and insert seed data

[A Gilmore]

On 11-03-22 10:40 AM, Simon Slavin wrote:
>
> Or just do a 'SELECT id FROM whatever LIMIT 1'.  If you get any error, it 
> doesn't exist, so create it and fill it.
>
> Or look in sqlite_master for an entry for the TABLE.
>
> Simon.

Could also use INSERT OR IGNORE statements for the seed data if the rows 
would conflict.

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