Keith, this definitely explains the observed time as it is relative to
count(a)*count (ab)**2, thus non-linear.
And a correlated sub-query is generally recalculated for each row.
But I do not agree with everything.
In my example it is correlated to the outermost query, and not to the
sub-que
Well of course. You are aware that a correlated subquery means "for each
candidate result execute the query"?
So as you have formulated the query it means:
for each row in a
compute the result count which
for each ab candidate row
calculate whether it is the minimum
Also of note is that when you make an index on ab (size), your original query,
unchanged, becomes about 3 times faster than my modification. I'm not sure on
what it looks at to decide if a temporary autoindex is worth it, but in this
case it would have been.
When you move back to your more comp
I acknowledge you said you weren't so much interested in an alternative
solution, but...
How about something like
select a, min(size) as minSize, recCount
from
(select a, size, count(*) as recCount
from a inner join ab
using (a)
group by a, size)
group by a;
The inner one will group by
On Aug 8, 2013, at 3:40 PM, Christopher W. Steenwyk wrote:
> This database is generated once, and then queried and interrogated multiple
> times. So I am most concerned with read speed and not with writing or
> updating.
Ohohoho… in that case… I have some snake oil to sell you, Dear Sir!
If yo
I just uploaded the output from sqlite3_analyze to dropbox.
On Thu, Aug 8, 2013 at 9:40 AM, Christopher W. Steenwyk wrote:
> Here is the data from stat1:
>
> "tbl", "idx", "stat"
> "metrics", "metrics_idx", "68682102 2 2 2"
> "metrics", "metrics_frame_idx", "68682102 2"
> "metrics", "sqlite_aut
Here is the data from stat1:
"tbl", "idx", "stat"
"metrics", "metrics_idx", "68682102 2 2 2"
"metrics", "metrics_frame_idx", "68682102 2"
"metrics", "sqlite_autoindex_mobileye_ldw_metrics_1", "68682102 230 1"
"object_characteristics", "object_characteristics_idx", "1148344 164050
31899 1"
"object_
On Wed, 7 Aug 2013 23:13:41 +0200
Petite Abeille wrote:
> On Aug 7, 2013, at 9:25 PM, Christopher W. Steenwyk
> wrote:
>
> > Ah, sorry about the attachments, you can find the files here:
> > https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb
>
> Ah, also, your schema has a very, hmmm, Entity?att
On Wed, Aug 7, 2013 at 2:54 PM, Christopher W. Steenwyk wrote:
> Hi,
>
> I have been working on a large database and its queries now for several
> weeks and just can't figure out why my query is so slow. I've attached the
> schema, my query, and the results of EXPLAIN QUERY from sqliteman.
>
> A
On Aug 7, 2013, at 9:25 PM, Christopher W. Steenwyk wrote:
> Ah, sorry about the attachments, you can find the files here:
> https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb
Ah, also, your schema has a very, hmmm, Entity–attribute–value (EAV) smell to
it (object, attribute, types, values, char
On Aug 7, 2013, at 9:25 PM, Christopher W. Steenwyk wrote:
> Ah, sorry about the attachments, you can find the files here:
> https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb
>
> And yes, as the final part of the DB creation I do run ANALYZE. And I do
> think the indexes are correct for the quer
Ah, sorry about the attachments, you can find the files here:
https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb
And yes, as the final part of the DB creation I do run ANALYZE. And I do
think the indexes are correct for the query.
On Wed, Aug 7, 2013 at 3:07 PM, Petite Abeille wrote:
>
> On Aug 7
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 07/08/13 11:54, Christopher W. Steenwyk wrote:
> I have been working on a large database and its queries now for
> several weeks and just can't figure out why my query is so slow. I've
> attached the schema, my query, and the results of EXPLAIN QUER
On 7 Aug 2013, at 7:54pm, Christopher W. Steenwyk wrote:
> I've attached the
> schema, my query, and the results of EXPLAIN QUERY from sqliteman.
Sorry, attachments don't work on this list (we don't want everyone sending us
their homework). Could you instead just post your SELECT command and
On Aug 7, 2013, at 8:54 PM, "Christopher W. Steenwyk"
wrote:
> The attached query takes over 6 days to run.
“Patience – A minor form of despair, disguised as a virtue.”
Also… attachments are stripped out by the mailing list. You may want to try to
inline them instead.
___
If your table doesn't have a primary key, this look like your table aren't
normalized, maybe you can try broke this table in 2 tables like:
Your definition:
CREATE TABLE table1(id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year
INTEGER);
indexes : index1( name ), index2( id2 ), index
> The year can also be different but Im interested only in the latest year. I
> use the GROUP because I want unique id and if I add year in GROUP BY I can
> get it repeated.
If you're interested in the latest year then your query is totally
wrong because it returns random year. If you saw what you
On Wed, Jun 30, 2010 at 2:40 AM, Pavel Ivanov wrote:
> > The query return the apropiate values as always the id -> id2 relation is
> > the same and id -> name and id2 -> name2.
>
> So your id maps uniquely to id2, name and name2. But what about year?
> What value of year do you want to be used in
> The query return the apropiate values as always the id -> id2 relation is
> the same and id -> name and id2 -> name2.
So your id maps uniquely to id2, name and name2. But what about year?
What value of year do you want to be used in sorting?
Anyway try to change query as "GROUP BY id, name2, ye
On 30 Jun 2010, at 12:32am, J. Rios wrote:
> On Wed, Jun 30, 2010 at 12:13 AM, Simon Slavin wrote:
>>
>> A good index would be
>>
>> name2, year, id
>>
>> That's all three columns in the same index, not three separate indexes one
>> on each column.
>
> I did the test and EXPLAIN QUERY PLAN
On Wed, Jun 30, 2010 at 12:13 AM, Simon Slavin wrote:
>
> None of your indexes are much use for this SELECT command. Imagine trying
> to do the SELECT command yourself and you'll quickly see that whichever of
> the supplied indexes you use you're left trying to sort a great deal of
> records by h
On 30 Jun 2010, at 12:05am, J. Rios wrote:
> Sorry for posting on top of the message,
No problem. Adding new text at the bottom of a post, and trimming what you
quote just enough that people understand your new text, make your own message
clear and encourage people to reply to it. Look at wh
On Wed, Jun 30, 2010 at 12:02 AM, Simon Slavin wrote:
>
> On 29 Jun 2010, at 11:57pm, J. Rios wrote:
>
> >>> I have created the next indexes : index1( name ), index2( id2 ),
> index3(
> >>> name2 );
>
> Those are very unlikely to be of any use at all. They're probably a waste
> of space.
>
> > I
The query return the apropiate values as always the id -> id2 relation is
the same and id -> name and id2 -> name2. I keep them in the same table to
speed up other queries that are now very quick as uses indexes for the
ordering but in this SELECT the GROUP BY makes the difference and the SORT
is g
On 29 Jun 2010, at 11:57pm, J. Rios wrote:
>>> I have created the next indexes : index1( name ), index2( id2 ), index3(
>>> name2 );
Those are very unlikely to be of any use at all. They're probably a waste of
space.
> Its not the primary Key. There are more fields but the index on id is
> cr
Its not the primary Key. There are more fields but the index on id is
created also. Sorry I missed it in the post.
If I do a EXPLAIN QUERY PLAN sqlite tells me its going to use the id INDEX.
But the sorting is slow.
Thanks in advance
On Tue, Jun 29, 2010 at 5:32 PM, Jim Morris wrote:
> You als
You also haven't specified a primary key, i.e. on "id"
On 6/28/2010 11:24 PM, J. Rios wrote:
> I have the next table
>
> table1( id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER
> );
>
> I have created the next indexes : index1( name ), index2( id2 ), index3(
> name2 );
>
>
_
> SELECT id, name, id2, name2 GROUP BY id ORDER BY name2, year DESC LIMIT 0,
> 15
>
> How can I make it faster?
First of all your query should return nonsense in any field except id.
I bet it will also return different results (for the same ids)
depending on what LIMIT clause you add or don't add
On Tue, 23 Sep 2008 14:37:11 -0400,
Enrique Ramirez wrote:
>On Tue, Sep 23, 2008 at 1:16 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
>> Steps to take (you need all of them, except 1):
>>
>> 1) Use v6.2.3
>>
>
>Probably meant to say 1) Use v3.6.2?
Oops, yes. Or even better: v3.6.3
--
( Kees Nuy
On Tue, Sep 23, 2008 at 1:16 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
> Steps to take (you need all of them, except 1):
>
> 1) Use v6.2.3
>
Probably meant to say 1) Use v3.6.2?
--
// --
Enrique Ramirez Irizarry
Lead Developer
Indie Code Labs
http://www.indieco
On Mon, 22 Sep 2008 13:48:42 -0700, Jason wrote:
>Hello everyone,
>
>Hoping that I could get some help with a performance problem.
>Using version 3.5.2
>
>Here are the tables:
>CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT)
>CREATE TABLE Keywords4Objects (ObjectId INTEGER, Keywor
that particular
point on that particular machine, all else behaves normal.
RBS
-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
Sent: 19 January 2008 14:12
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Slow query on one machine
On Jan 18, 2008 3:32 PM, RB Smissaert <[EMAIL P
On Jan 18, 2008 3:32 PM, RB Smissaert <[EMAIL PROTECTED]> wrote:
> The application
> that runs this is exactly the same on both machines. The slow machine is
> actually slightly slower specification wise, but that can't explain the huge
> differences in timings.
>
Have you run spinrite ( a disk di
Ignore this as I found out what the trouble was.
I dropped 2 indices from the table ADDRESSLINK as they were on fields
where the values were nearly all the same. Forgot now what the technical
term for it is, but it solved it all and now very fast.
Great software this SQLite!
RBS
-Original Mes
> What is the name of your database (with extension please) ?
> There are chances that such extension is registered into Windows XP
> crap called System Restore and each time on start it detect that this
> file is changed and create a restore snapshot for it.
List given at
http://msdn.microsof
Geoff Simonds wrote:
Thanks to everyone for all the help on this problem. I am going to try
creating a new thread to touch the tables at startup.
Chris Schirlinger wrote:
We have the same issue, to get around it we fire a thread when the
program starts, intelligently "touching" every table th
Thanks to everyone for all the help on this problem. I am going to try
creating a new thread to touch the tables at startup.
Chris Schirlinger wrote:
We have the same issue, to get around it we fire a thread when the
program starts, intelligently "touching" every table that the user is
like
We have the same issue, to get around it we fire a thread when the
program starts, intelligently "touching" every table that the user is
likely to access (As Michael Sizaki already mentioned a select
count(last_column) from big_table; will do it)
Since a user is very unlikely to run a program a
Geoff Simonds wrote:
The app is running on Windows XP machines
Is it possible that indexing services are enabled and XP is trying to
index the database file?
monds <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, January 19, 2006 7:52:55 AM
Subject: Re: [sqlite] Slow query after reboot
My table contains about 500,000 rows and 4 columns, not all that much
data. The overall size of the db file is 35 mb. Does 15 - 20 seconds
sound ri
Thanks for the info and suggestions Michael. I will give this a try.
Michael Sizaki wrote:
Geoff Simonds wrote:
My table contains about 500,000 rows and 4 columns, not all that much
data. The overall size of the db file is 35 mb. Does 15 - 20
seconds sound right to load from disk into mem
Geoff Simonds wrote:
My table contains about 500,000 rows and 4 columns, not all that much
data. The overall size of the db file is 35 mb. Does 15 - 20 seconds
sound right to load from disk into memory?
Yes it does. The problem is, that your query is probably
not reading sequentially from di
- Original Message -
From: "Geoff Simonds" <[EMAIL PROTECTED]>
My table contains about 500,000 rows and 4 columns, not all that much
data. The overall size of the db file is 35 mb. Does 15 - 20 seconds
sound right to load from disk into memory?
I can't tell you that until the foll
My table contains about 500,000 rows and 4 columns, not all that much
data. The overall size of the db file is 35 mb. Does 15 - 20 seconds
sound right to load from disk into memory?
Robert Simpson wrote:
- Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]>
The app is
- Original Message -
From: "Geoff Simonds" <[EMAIL PROTECTED]>
The app is running on Windows XP machines and I assume that disk files are
cached. The strange thing is that the time it takes for the initial read
into RAM after install and first use is significantly shorter than after
The app is running on Windows XP machines and I assume that disk files
are cached. The strange thing is that the time it takes for the initial
read into RAM after install and first use is significantly shorter than
after a reboot. For example, if you just installed the app and start
it, the f
On 1/19/06, Geoff Simonds <[EMAIL PROTECTED]> wrote:
> I have created a client application that is always running on a users
> desktop. The application accepts user input and then uses SQLite to
> perform a few simple queries against a single db file that contains 4
> tables. The performance is fan
47 matches
Mail list logo