Following is the query that I use for getting a fixed number of records from
a database with millions of records:-
select * from myTable LIMIT 100 OFFSET 0
What I observed is, if the offset is very high like say 9, then it takes
more time for the query to execute. Following is the time
On 4 Sep 2012, at 3:56pm, sattu linkpr...@rocketmail.com wrote:
What I observed is, if the offset is very high like say 9, then it takes
more time for the query to execute. Following is the time difference between
2 queries with different offsets:
select * from myTable LIMIT 100
sattu linkpr...@rocketmail.com wrote:
select * from myTable LIMIT 100 OFFSET 0 //Execution Time is less than
1sec
select * from myTable LIMIT 100 OFFSET 95000 //Execution Time is almost
15secs
http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
--
Igor Tandetnik
Suppose you have a table like this:
CREATE TABLE tab(ID, A, B, C, ..., MTIME);
The ID entries are not unique. For each ID occurrence, the values for A,
B, C, and so forth might (or might not) be different. We do queries like
this:
SELECT id, a, b, ..., mtime
FROM tab
ORDER BY
SELECT id, a, b, ..., mtime
FROM tab
WHERE rowid IN (
SELECT id, min(mtime)
FROM tab
GROUP BY id
)
ORDER BY mtime DESC;
On Wed, Sep 5, 2012 at 4:03 PM, Richard Hipp d...@sqlite.org wrote:
SELECT id, a, b, ..., min(mtime)
FROM tab
GROUP
Sorry, that won't work.
On Wed, Sep 5, 2012 at 4:12 PM, Baruch Burstein bmburst...@gmail.comwrote:
SELECT id, a, b, ..., mtime
FROM tab
WHERE rowid IN (
SELECT id, min(mtime)
FROM tab
GROUP BY id
)
ORDER BY mtime DESC;
On Wed, Sep 5, 2012
On Wed, Sep 5, 2012 at 9:12 AM, Baruch Burstein bmburst...@gmail.comwrote:
SELECT id, a, b, ..., mtime
FROM tab
WHERE rowid IN (
SELECT id, min(mtime)
FROM tab
GROUP BY id
)
ORDER BY mtime DESC;
In the actual application, tab is not a real
On 5 Sep 2012, at 2:03pm, Richard Hipp d...@sqlite.org wrote:
*The problem to solve* is this: We want to restrict the output of the
query above to show only the entry with the smallest MTIME for each
distinct ID.
As of SQLite version 3.7.11
http://www.sqlite.org/releaselog/3_7_11.htmlthe
Temporary table? Then you will have rowids.
On Wed, Sep 5, 2012 at 4:16 PM, Richard Hipp d...@sqlite.org wrote:
On Wed, Sep 5, 2012 at 9:12 AM, Baruch Burstein bmburst...@gmail.com
wrote:
SELECT id, a, b, ..., mtime
FROM tab
WHERE rowid IN (
SELECT id, min(mtime)
Richard Hipp d...@sqlite.org wrote:
Suppose you have a table like this:
CREATE TABLE tab(ID, A, B, C, ..., MTIME);
The ID entries are not unique. For each ID occurrence, the values for A,
B, C, and so forth might (or might not) be different. We do queries like
this:
SELECT id,
Does anyone know if there is a mailing list for the STL project?
This e-mail is for the sole use of the intended recipient and may contain
confidential or privileged information. Unauthorized use of its contents is
prohibited. If you have received this e-mail in error, please notify sender
Arbol One arbol...@gmail.com wrote:
Does anyone know if there is a mailing list for the STL project?
Which of these do you mean by STL?
http://en.wikipedia.org/wiki/Standard_Template_Library
http://en.wikipedia.org/wiki/State_logic
http://en.wikipedia.org/wiki/Subtitle_(captioning)
Am 05.09.2012 15:58, schrieb Igor Tandetnik:
Well, you could do something like this:
SELECT id, a, b, ..., mtime FROM tab t1
where mtime = (select min(mtime) from tab t2 where t2.id=t1.id)
ORDER BY mtime DESC;
Ah, nice ... this solves the problem of the potential
non-uniqueness of mtime...
On Wed, Sep 5, 2012 at 10:53 AM, Olaf Schmidt s...@online.de wrote:
Am 05.09.2012 15:58, schrieb Igor Tandetnik:
Well, you could do something like this:
SELECT id, a, b, ..., mtime FROM tab t1
where mtime = (select min(mtime) from tab t2 where t2.id=t1.id)
ORDER BY mtime DESC;
Ah,
Richard Hipp d...@sqlite.org wrote:
In the actual use case that inspired this question (and for which, after
seeing the alternatives, I think I'll stick with SQLite's magical
processing of min()) the mtime value is a floating point number, and we all
know the hazards of comparing floating
Am 05.09.2012 16:57, schrieb Richard Hipp:
On Wed, Sep 5, 2012 at 10:53 AM, Olaf Schmidt s...@online.de wrote:
Am 05.09.2012 15:58, schrieb Igor Tandetnik:
Well, you could do something like this:
SELECT id, a, b, ..., mtime FROM tab t1
where mtime = (select min(mtime) from tab t2 where
Hi sqlite-users,
I have a column in table 'alpha' which I would like to populate with data
from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in
sqlite, but we can
UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term =
alpha.term)
or we can create a
Thank you so much for all help. I really appreciate it.
/All the same, I'm guessing that
http://www.sqlite.org/src/info/39f763bfc0will fix your problem./
I gave this new src code from the src tree a shot, but I seem to be getting
the same behavior.
Interestingly though, I tried enforcing my
On 09/05/2012 06:36 PM, Arbol One wrote:
That would be the C++ Standard Template Library
http://en.wikipedia.org/wiki/Standard_Template_Library
I am learning the library and as a student I always have questions.
The STL isn't necessarily a project, as it is defined in the C++
standard and
On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
I have a column in table 'alpha' which I would like to populate with data
from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in
sqlite, but we can
UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term =
On Sep 5, 2012, at 3:03 PM, Richard Hipp d...@sqlite.org wrote:
SELECT id, a, b, ..., min(mtime)
FROM tab
GROUP BY id
ORDER BY min(mtime) DESC;
Perhaps something along these lines, i.e. a simple self-join:
selectfoo.*
from foo
join (
selectid,
Am 2012-09-05 18:53, schrieb esum:
Thank you so much for all help. I really appreciate it.
/All the same, I'm guessing that
http://www.sqlite.org/src/info/39f763bfc0will fix your problem./
I gave this new src code from the src tree a shot, but I seem to be
getting
the same behavior.
It is not mentioned by the docs that your trace callback will be called
encapsulated by a mutex - In other words: Are you sure that your trace callback
is threadsafe ?
Thanks for the suggestion Marcus. I have tried encapsulating the callback with
a mutex to make it threadsafe. However, that
Dear Igor,
Are you saying that
UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term
= alpha.term)
is just as efficient as it gets and equivalent to an update using join (in
other dbs)? That would helpful to know. I do kind of imagine some kind of
black magic... ;)
On Wed,
On 9/5/2012 4:11 PM, E. Timothy Uy wrote:
Are you saying that
UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term
= alpha.term)
is just as efficient as it gets and equivalent to an update using join (in
other dbs)?
I don't know what other DBMS are doing. In SQLite, this
Well, I think you want a where clause on your main UPDATE query. What you
wrote will set the frequency of every record in the alpha table to the value
from the beta table, for every record in the beta table that matches an alpha
record. (It's late, I'm tired and that's incoherent. I hope you
On 9/5/2012 4:20 PM, Rob Richardson wrote:
Well, I think you want a where clause on your main UPDATE query. What you
wrote will set the frequency of every record in the alpha table to the value
from the beta table, for every record in the beta table that matches an alpha
record. (It's late,
Igor, what happens if there are multiple hits for
SELECT frequency FROM beta WHERE beta.term
= alpha.term
(there aren't but, it is possible for there to be). Rob could be right in
a sense.
On Wed, Sep 5, 2012 at 1:28 PM, Igor Tandetnik itandet...@mvps.org wrote:
On 9/5/2012 4:20 PM, Rob
On 9/5/2012 4:32 PM, E. Timothy Uy wrote:
Igor, what happens if there are multiple hits for
SELECT frequency FROM beta WHERE beta.term
= alpha.term
There can't be multiple hits, or even a single hit. This statement will
not run, as it's not syntactically valid.
--
Igor Tandetnik
On 9/5/2012 4:35 PM, Igor Tandetnik wrote:
On 9/5/2012 4:32 PM, E. Timothy Uy wrote:
Igor, what happens if there are multiple hits for
SELECT frequency FROM beta WHERE beta.term
= alpha.term
There can't be multiple hits, or even a single hit. This statement will
not run, as it's not
On Sep 5, 2012, at 10:38 PM, Igor Tandetnik itandet...@mvps.org wrote:
A select statement that would most closely resemble your update statement
would look like this:
select frequency, (select frequency from beta where beta.term = alpha.term)
from alpha;
This statement will always
On 9/5/2012 4:54 PM, Petite Abeille wrote:
On Sep 5, 2012, at 10:38 PM, Igor Tandetnik itandet...@mvps.org wrote:
A select statement that would most closely resemble your update statement would
look like this:
select frequency, (select frequency from beta where beta.term = alpha.term)
from
On Sep 5, 2012, at 11:00 PM, Igor Tandetnik itandet...@mvps.org wrote:
And such statement should raise an exception if the scalar sub-query returns
multiple rows, no?
Definitely not in SQLite. I don't believe it would do that in other DBMS
either, but won't bet on it. SQLite would take
On Sep 5, 2012, at 4:57 PM, Richard Hipp d...@sqlite.org wrote:
I think I'll stick with SQLite's magical processing of min())
I think I'll stick with SQLite's magical processing of mine -- there, I fixed
it for you :P
the mtime value is a floating point number, and we all know the hazards
I think what you are asking is whether an update through a cursor:
Update alpha
Set alpha.value = beta.value
From alpha, beta
Where alpha.key = beta.key
(which is how you would express an update of a join table in other DBMS's)
Is equivalent to (and will be executed as)
Update alpha
Update alpha
Set alpha.value = beta.value
From alpha, beta
Where alpha.key = beta.key
(which is how you would express an update of a join table in other DBMS's)
Is equivalent to (and will be executed as)
Update alpha
Set alpha.value = (select beta.value from beta where
I like replying to myself, obviously ...
Update alpha
Set alpha.value = (select beta.value from beta where beta.key = alpha.key)
Where exists (select * from beta where beta.key = alpha.key)
Could be more efficiently:
Update alpha
Set alpha.value = coalesce((select beta.value from beta
Igor Tandetnik wrote:
On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
I have a column in table 'alpha' which I would like to populate with data
from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in
sqlite, but we can
UPDATE alpha SET frequency = (SELECT frequency FROM beta
On 6 Sep 2012, at 2:53am, Yuriy Kaminskiy yum...@gmail.com wrote:
Igor Tandetnik wrote:
On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
Will the database really be doing a select in beta for
every single line in alpha?
Yes - same as when implementing a join. How do you think a join is
On 9/5/2012 9:53 PM, Yuriy Kaminskiy wrote:
Igor Tandetnik wrote:
On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
I have a column in table 'alpha' which I would like to populate with data
from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in
sqlite, but we can
UPDATE alpha SET
sqlite create table alpha (frequency, term);
sqlite create table beta (term, frequency);
sqlite create index betaterm on beta(term);
sqlite .explain
sqlite explain query plan update alpha set frequency = (select frequency from
beta where beta.term = alpha.term);
sele order from deta
41 matches
Mail list logo