Re: [sqlite] Index and ORDER BY

Yes, this has been my experience as well. I've tried 3.5.6 and 3.5.9. Jeff Alexey Pechnikov wrote: > В сообщении от Tuesday 01 July 2008 23:47:50 [EMAIL PROTECTED] написал(а): > >> On Tue, 1 Jul 2008, Alexey Pechnikov wrote: >> >>> Is any difference between "CREATE INDEX ev_idx ON

Re: [sqlite] Index and ORDER BY

ind a matching type. Thanks, Jeff D. Richard Hipp wrote: > On Jul 1, 2008, at 2:17 PM, Jeff Gibson wrote: > > >> I'm including a copy of Alexey's relevant message below. Unless I >> misunderstand, he has a test case that demonstrates that for the >> table: >>

Re: [sqlite] Index and ORDER BY

I'm including a copy of Alexey's relevant message below. Unless I misunderstand, he has a test case that demonstrates that for the table: CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER) the query: SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT

Re: [sqlite] Index and ORDER BY

When I try a similar query (i.e, no type comparison), I get the same results as you: sqlite> SELECT eid,type FROM EVENTS WHERE eid<=3261976 ORDER BY eid DESC LIMIT 1; 3261976|21 CPU Time: user 0.00 sys 0.027996 sqlite> EXPLAIN QUERY PLAN SELECT eid,type FROM EVENTS WHERE eid<=3261976

Re: [sqlite] Index and ORDER BY

y instantaneous. This is part of a GUI application, so a 10-second delay is highly undesirable. Any other suggestions? Thanks, Jeff D. Richard Hipp wrote: > On Jun 27, 2008, at 6:28 PM, Jeff Gibson wrote: > > >> I have a large table and a two column index: >> >&g

[sqlite] Index and ORDER BY

I have a large table and a two column index: CREATE TABLE events (eid INTEGER PRIMARY KEY, time INTEGER, aid INTEGER, subtype INTEGER, type INTEGER, tid INTEGER, verbose

[sqlite] schema optimization question

I'm sorry if this is an obvious question - I'm new to databases. I have an application where the database is used to log a large number of simulation events. The database is written once and read many times (i.e., there are never any inserts or updates after database creation). The three

Re: [sqlite] schema design question

Thanks for all the suggestions. My schema is now a lot cleaner, and my application runs 30% faster! Jeff Richard Klein wrote: >> Jeff Gibson wrote: >> >>> One thing your earlier suggestion brought up. The way I was hooking up >>> tables before

Re: [sqlite] schema design question

s." >> >> The creation of an index would seem to imply an O(log N) search on each >> insertion, so you should be okay. >> >> My advice would be to try it and see. If table creation takes too long, >> you can always remove the UNIQUE constraint, and then wri

Re: [sqlite] schema design question

I see. Fortunately my application simplifies this since the database is created once and read many times, but is never modified after creation time. Regarding constraints, I was thinking it might be helpful to add a few where applicable (whether foreign key constraints or even simple

Re: [sqlite] schema design question

Thanks! I'll give that a try. Jeff Richard Klein wrote: >> Whether or not the the secondary columns are needed is a function of one >> of the primary columns. That function involves values from another >> table, though, so the general case would require a join. That other >> table is

Re: [sqlite] schema design question

Whether or not the the secondary columns are needed is a function of one of the primary columns. That function involves values from another table, though, so the general case would require a join. That other table is small, however, so I generally cache it outside the database. Some

[sqlite] schema design question

I'm pretty new to databases, and I have a schema design question. I don't know enough about the guts of how sqlite works to know how to make some tradeoffs. I have a large (potentially millions of entries) table and it has 4 columns which are needed for every entry, and 4 more that are