Hi,
The index of (d,n,s) has improved the performance and is WAY better than
(n,s,d)
Thanks to everyone for helping me out.
>> So which is better ? An Index or a Primary Key ?
My index is not unique and hence I guess going to Primary Keys would slow
down inserts quite a lot. Please correct me i
On Sun, 20 Oct 2013 16:53:30 +0530
Raheel Gupta wrote:
> I need the inserts to be faster.
Faster than what? Why can the existing hardware not be replaced?
I would be interested to hear what you find out testing your
performance with the primary key in place. I doubt it will be very
differen
On Sun, 20 Oct 2013 17:08:09 +0400
Yuriy Kaminskiy wrote:
> > The new INDEX that I created on your suggestion with d,n,s solves
> > the problem.
> > Now the question is over Primary key whether that will slow it down
> > or not.
>
> Only question is whether you have *other* queries that prefers
On Sat, 19 Oct 2013 20:07:22 +0200
Clemens Ladisch wrote:
> > It seems to be using the the covering index which I guess is the
> > fastest way as Sqlite doesnt need to check the actual table. So why
> > would it slow down and use so much CPU in my last query ?
>
> In theory, a multi-column index
On 20 Oct 2013, at 12:23pm, Raheel Gupta wrote:
>> Yes, but they allow the searches to be faster. You are making it longer
>> to do INSERT but shorter to do SELECT. Which is best for you depends on
>> your purposes.
>
> I need the inserts to be faster.
> So which is better ? An Index or a Pri
Raheel Gupta wrote:
>> Yes, but they allow the searches to be faster. You are making it longer
>> to do INSERT but shorter to do SELECT. Which is best for you depends on
>> your purposes.
>>
>
> I need the inserts to be faster.
> So which is better ? An Index or a Primary Key ?
Is there any dif
>
> Yes, but they allow the searches to be faster. You are making it longer
> to do INSERT but shorter to do SELECT. Which is best for you depends on
> your purposes.
>
I need the inserts to be faster.
So which is better ? An Index or a Primary Key ?
The new INDEX that I created on your suggest
On 19 Oct 2013, at 8:30pm, Raheel Gupta wrote:
> Does SQLite support multi column primary keys ?
Yes.
> Also wouldnt primary keys actually slow down further inserts. I have
> queries to insert nearly 1 rows in one second. With larger database
> multi column primary keys might slow down rig
On 10/19/13, Raheel Gupta wrote:
> Does SQLite support multi column primary keys ?
Yes.
> Also wouldnt primary keys actually slow down further inserts. I have
> queries to insert nearly 1 rows in one second. With larger database
> multi column primary keys might slow down right ?
Not really
Hi,
>> First, consider if some combination of those columns constitute a
primary key. That would be stronger than a simple index.
Does SQLite support multi column primary keys ?
Also wouldnt primary keys actually slow down further inserts. I have
queries to insert nearly 1 rows in one second.
On Sat, 19 Oct 2013 21:21:44 +0530
Raheel Gupta wrote:
> CREATE INDEX map_index ON map (n, s, d, c, b);
>
> The above table is having nearly 600 Million Records and is of size
> 26 GB. The column 'n' is representing Numbers of Blocks on the file
> system. 's' stands for Snapshot ID.
> 'd' is dev
On 19 Oct 2013, at 7:29pm, Raheel Gupta wrote:
> My current index is actually in the correct order of my query.
> I use 'n' and 's' and they are the first in the query.
This isn't how SQLite works. It's more clever than that. SQlite will analyze
your WHERE clause and do lots of clever choppi
> I cannot definitely solve your problem but I can think of some things to
> try. First, do these:
>
> ANALYZE;
> CREATE INDEX map_dsn ON map (d, s, n);
> CREATE INDEX map_dns ON map (d, n, s);
>
> then execute the same SELECT. Does it have the same problem ? Does the
> EXPLAIN QUERY PLAN tell y
On 19 Oct 2013, at 7:16pm, Fabian Büttner wrote:
> Shouldn't ANALZYE be run _after_ creating the indexes?
"The ANALYZE command gathers statistics about tables and indices"
Whoops. Yes. Thanks.
Simon.
___
sqlite-users mailing list
sqlite-users@sqli
>> See if the situation changes if you drop all those single quotes around
your constants. Why are you comparing integer values to string literals?
Tried that and it doesnt change.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8
I cannot definitely solve your problem but I can think of some things to try.
First, do these:
ANALYZE;
CREATE INDEX map_dsn ON map (d, s, n);
CREATE INDEX map_dns ON map (d, n, s);
then execute the same SELECT. Does it have the same problem ? Does the
EXPLAIN QUERY PLAN tell you which of
Raheel Gupta wrote:
> > CREATE INDEX map_index ON map (n, s, d, c, b);
> >
> > SELECT n, c, b, s FROM map WHERE s <= '326' AND s NOT IN (0) AND d = '15'
> > AND n >= '15591116' ORDER BY n ASC LIMIT 0, 32768
>
> 0|0|0|SEARCH TABLE map USING COVERING INDEX map_index (n>?) (~4166 rows)
> 0|0|0|EXECUT
You may want to put the columns with the highest selectivity first in
your index.
The device 15 has nearly 10 entries in the table while the remaining of
the 600 Million records belong to another device.
E.g., CREATE INDEX map_index ON map (d, ...);
Also, you should run ANALYZE map so that
On 19 Oct 2013, at 6:54pm, Raheel Gupta wrote:
> Here is the output :
> 0|0|0|SEARCH TABLE map USING COVERING INDEX map_index (n>?) (~4166 rows)
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
> I am not sure how can I optimize this ?
> (Also I checked again and there are 4166 rows in this last result and no
On 10/19/2013 11:51 AM, Raheel Gupta wrote:
SELECT n, c, b, s FROM map WHERE s <= '326' AND s NOT IN (0) AND d = '15'
AND n >= '15591116' ORDER BY n ASC LIMIT 0, 32768
See if the situation changes if you drop all those single quotes around
your constants. Why are you comparing integer values t
Hi,
Here is the output :
0|0|0|SEARCH TABLE map USING COVERING INDEX map_index (n>?) (~4166 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
I am not sure how can I optimize this ?
(Also I checked again and there are 4166 rows in this last result and not
1568 as per my last email.)
It seems to be using the t
On 19 Oct 2013, at 4:51pm, Raheel Gupta wrote:
> CREATE TABLE map (
>n BIGINT NOT NULL DEFAULT 0,
>s INT(5) NOT NULL DEFAULT 0,
>d INT(5) NOT NULL DEFAULT 0,
>c INT(1) NOT NULL DEFAULT 0,
>b UNSIGNED BIGINT NOT NULL DEFAULT 0
>)
Hi,
I am facing a peculiar issue with SQLITE.
The following is my table structure :
CREATE TABLE map (
n BIGINT NOT NULL DEFAULT 0,
s INT(5) NOT NULL DEFAULT 0,
d INT(5) NOT NULL DEFAULT 0,
c INT(1) NOT NULL DEFAULT 0,
b UNSIGNED BIGINT N
23 matches
Mail list logo
