RB Smissaert wrote:
I think an application that
would produce all the needed indexes based on the table and all the possible
queries would be helpful. Anybody done such an app?
_All_ possible queries? Not practical for any significant number of
columns. N factorial gets big fast.
The
Ok, I guessed something like that were the case, but what I didn't get
was the purpose of the logN, without knowing the base for that. So why not
simply something like: if (N / m) > 2 then most likely index will be
helpful.
It sure is tricky to add these indexes as getting it wrong will seriously
RB Smissaert <[EMAIL PROTECTED]>
wrote:
But then if the base of the logarithm doesn't matter then
how is this equation going to help you?
m==N/logN
So, basically it comes down to some experimenting?
Well, it tells you that if m is much smaller than N (say, by two orders
of magnitude or
--- Alain Bertrand <[EMAIL PROTECTED]> wrote:
> In a Qt4 application, I have the following bit of code :
>
> QString s;
> bool b;
> QSqlQuery q;
> QSqlError err;
> s= "CREATE TABLE ttd_bdata (sheetId integer primary key,";
> s+="creation_date date, modif_date date,";
>
Hi all
In a Qt4 application, I have the following bit of code :
QString s;
bool b;
QSqlQuery q;
QSqlError err;
s= "CREATE TABLE ttd_bdata (sheetId integer primary key,";
s+="creation_date date, modif_date date,";
s+="speciesId integer,USDA_zone integer,";
But then if the base of the logarithm doesn't matter then
how is this equation going to help you?
m==N/logN
So, basically it comes down to some experimenting?
RBS
-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: 04 August 2007 21:32
To: SQLite
Subject:
RB Smissaert <[EMAIL PROTECTED]>
wrote:
OK, will have a look at the wiki.
There's no "m" on the right hand side.
m equals N divided by logarithm of N.
What is the base of that logarithm then?
Doesn't matter. All calulations shown are order of magnitude, only
accurate modulo multiplication
At 15:28 04/08/2007 -0400, Igor Tandetnik wrote:
UPDATE mytable SET timestamp = old.timestamp + 1 WHERE rowid=new.rowid;
Great :-) Thanks a lot. For those interested in doing the same thing:
=
create table mytable (id INTEGER AUTO PRIMARY KEY, name VARCHAR(50),
timestamp
OK, will have a look at the wiki.
> There's no "m" on the right hand side.
> m equals N divided by logarithm of N.
What is the base of that logarithm then?
RBS
-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: 04 August 2007 21:03
To: SQLite
Subject: [sqlite]
RB Smissaert <[EMAIL PROTECTED]>
wrote:
Thanks; I have seen this O(N) etc. explanations a lot, but not sure
what they exactly mean.
http://en.wikipedia.org/wiki/Big_O_notation
Roughly, we say that an algorithm has complexity O(N) (where N is the
size of its input) when there exists some
Thanks; I have seen this O(N) etc. explanations a lot, but not sure
what they exactly mean.
Does it in this case simply mean O * N and O * (m log N) ?
> and for each entry would perform a logN
Does the logN here mean m log N or something else?
> m==N/logN
Ditto, does this mean break even point
It should be possible to do what you're trying to do as of 3.3.8.
See the Tcl examples at the bottom of this patch:
Allows UPDATE, INSERT, and DELETEs to occur while
a SELECT is pending on the same table.
http://www.sqlite.org/cvstrac/chngview?cn=3355
Post a complete self-contained C
Gilles Ganault
wrote:
So, I guess the solution is to write a trigger. Problem is, I've
never used
triggers before, so I don't really know how to get around to fetching
the
previous value of a timestamp column and increment it whenever a
record is
updated:
CREATE TRIGGER update_timestamp
At 13:05 04/08/2007 +, [EMAIL PROTECTED] wrote:
2.8.x support triggers.
Good to know :-) So I don't have to upgrade everyone to 3.x then. I did
try to create a self-incrementing timestamp column with 2.8.x but it
doesn't seem to be handle more than one such column:
RB Smissaert <[EMAIL PROTECTED]>
wrote:
One thing I am not sure about yet is when an index would be helpful
in the
first place in relation to the data in the field.
I understand an index is going to help little if the values in a
particular
field can only for example be 1 or 0, but roughly when
RB Smissaert <[EMAIL PROTECTED]>
wrote:
How does the field order in indexes work with joins?
So for example given the query:
select
t1.a,
t1.b,
t2.c
from
table1 t1 inner join table2 t2 on
(t1.id1 = t2.id2)
where
t1.a = 'abc'
would the index need to be
(a, id1)
or
(id1, a)
Doesn't matter.
How does the field order in indexes work with joins?
So for example given the query:
select
t1.a,
t1.b,
t2.c
from
table1 t1 inner join table2 t2 on
(t1.id1 = t2.id2)
where
t1.a = 'abc'
would the index need to be
(a, id1)
or
(id1, a)
Does the field order in the tables have anything to do with
> Index columns can only be used from left to right, with no skips,
> to satisfy the conditions of the query.
Ah, yes, I forgot about that one. So, I will need some more indexes.
RBS
-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: 04 August 2007 15:49
To:
RB Smissaert <[EMAIL PROTECTED]>
wrote:
So, basically it is best to make one large index (apart from the
primary
integer key?) that includes all fields that could be in a WHERE
clause or a
JOIN or a GROUP BY or a HAVING or an ORDER BY?
That depends on the queries you want to speed up. Index
Hi,
What we are doing in short is below (using Sqlite 3.3.13).
Select id, data from msgTable; // ok - returns 100
tid = sqlite3_column_int;
data = sqlite3_column_bytes;
// make a copy of the bytes
// update the byte values
update
Thanks, that was very useful.
I didn't realize that table values could be obtained from the index.
I suppose it makes sense when you think about it.
So, basically it is best to make one large index (apart from the primary
integer key?) that includes all fields that could be in a WHERE clause or a
RB Smissaert <[EMAIL PROTECTED]>
wrote:
I get this query plan (explain query plan):
order from detail
0 0 TABLE ENTRY AS E WITH INDEX
IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID
My question is why doesn't it pick the
Hallo all,
i read a lot about the plans of creating an spatial index for gis
applications.
This would be very interessting for my, because i am writing a diploma
thesis
on developement an routing-algorithm on an mobile client with SQLite and
the data of openstreetmap.
there for i am
Given this table:
CREATE TABLE AMorb37F6_E
([PATIENT_ID] INTEGER,
[ENTRY_ID] INTEGER PRIMARY KEY,
[READ_CODE] TEXT,
[ADDED_DATE] TEXT,
[START_DATE] TEXT)
And these indexes:
IDX10$ENTRY$PATIENT_ID
IDX11$ENTRY$TERM_TEXT
IDX12$ENTRY$READ_CODE$ADDED_DATE
Gilles Ganault <[EMAIL PROTECTED]> wrote:
> Hello
>
> I was wondering if SQLite 2.8.x was able to update a column
> automatically
> when performing either INSERT or UPDATE, or if this can only be done with
> triggers in 3.x?
>
2.8.x support triggers.
--
D. Richard Hipp <[EMAIL
Hello
I was wondering if SQLite 2.8.x was able to update a column automatically
when performing either INSERT or UPDATE, or if this can only be done with
triggers in 3.x?
For instance, when adding a record, I need to have a counter in the third
column be incremented by one:
26 matches
Mail list logo
