When I did the index test I did not have the second table in the DB.
Just the complete large table (id, topics, definition). I also didn't have
order by random() limit 1
In the select statment. The data is alread in alphabetical order, but I'll try
that anyway.
I am going to be away from the
At 07:53 21/03/2012, Max Vlasov wrote:
On Wed, Mar 21, 2012 at 1:33 AM, Tim Morton wrote:
> So it seems the index is no help;
>
Are you sure you did as Simon explained?
Becausem my tests shows the trick works... (sqlite 3.7.10)
The trick works, but i think he didn't timed what he should tim
On Wed, Mar 21, 2012 at 1:33 AM, Tim Morton wrote:
> So it seems the index is no help;
>
Are you sure you did as Simon explained?
Becausem my tests shows the trick works... (sqlite 3.7.10)
Create table [TestTable] ([Id] integer primary key, [topics] Text,
[Definition] Text);
CREATE TABLE [Sour
On 20 Mar 2012, at 9:33pm, Tim Morton wrote:
> So it seems the index is no help; a second topics table is a significant
> help; and a separate file with topics table a negligible help; and creation
> order is no help.
Interesting and a little unexpected. Good to see a user doing experimentat
I did some quick tests:
I took a 100,000 entry dictionary and made a single table
"create table dict (id INTEGER PRIMARY KEY, topics, def)"
I ran a "for loop" on the cursor object to extract all the topic column
data and put in in a Python list .
cur.execute('SELECT topics FROM dict')
start
Ah, very good. Thanks to you both.
I will definitly try this. Sounds like it may help.
Tim
On 3/20/2012 2:36 PM, Simon Slavin wrote:
On 20 Mar 2012, at 6:11pm, Tim Morton wrote:
I may try this, but the topics are usually just one word each so I doubt an
index could reduce it much.
Max'
On 20 Mar 2012, at 6:11pm, Tim Morton wrote:
> I may try this, but the topics are usually just one word each so I doubt an
> index could reduce it much.
Max's trick is something that takes advantage of how SQLite works entirely. If
you do a SELECT like
SELECT id,topics FROM dictionary ORDER
Thanks,
I may try this, but the topics are usually just one word each so I doubt
an index could reduce it much.
Tim
On 3/20/2012 1:52 PM, Max Vlasov wrote:
Hi, Tim
On Tue, Mar 20, 2012 at 6:21 PM, Tim Morton wrote:
Is there a way to read only the part of the file that has the queried
Hi, Tim
On Tue, Mar 20, 2012 at 6:21 PM, Tim Morton wrote:
>
> Is there a way to read only the part of the file that has the queried
> table/column thus saving time and hard dive grinding?
>
>
There is also a trick. You can create an index on 'topics' column and
perform your first reading forcin
Thanks, Simon, for your detailed answer.
I will try the suggestions you and Michael supplied and see if there is
any significant inprovement.
Tim
On 3/20/2012 11:13 AM, Simon Slavin wrote:
On 20 Mar 2012, at 2:21pm, Tim Morton wrote:
My app reads dozens of SQLite databases ranging in size
On 20 Mar 2012, at 2:21pm, Tim Morton wrote:
> My app reads dozens of SQLite databases ranging in size from 1MB to 100MB
> with a simple table structure like,
>
> "create table dictionary(id INTEGER PRIMARY KEY, topics, definition)"
>
> On startup the app reads all the databases to extract th
: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Morton [t...@preservedwords.com]
Sent: Tuesday, March 20, 2012 9:44 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Optimize Table Access Efficiency
Thanks for the reply,
"Lengthy&qu
qlite-users-boun...@sqlite.org] on
behalf of Tim Morton [t...@preservedwords.com]
Sent: Tuesday, March 20, 2012 9:21 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Optimize Table Access Efficiency
Greetings,
My app reads dozens of SQLite databases ranging in size from 1MB to
100MB with
o: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Optimize Table Access Efficiency
Greetings,
My app reads dozens of SQLite databases ranging in size from 1MB to
100MB with a simple table structure like,
"create table dictionary(id INTEGER PRIMARY KEY, topics, definition)"
On star
Greetings,
My app reads dozens of SQLite databases ranging in size from 1MB to
100MB with a simple table structure like,
"create table dictionary(id INTEGER PRIMARY KEY, topics, definition)"
On startup the app reads all the databases to extract the "topics"
column data. With the DB configure
pp [mailto:[EMAIL PROTECTED]
> >Odoslané: 15. apríla 2005 11:15
> >Komu: sqlite-users@sqlite.org
> >Predmet: Re: [sqlite] optimize table
> >
> >
> >On Fri, 2005-04-15 at 06:15 -0400, Ken & Deb Allen wrote:
> >> I cannot speak for SQLITE for certain, b
have you some feeling on my problem?
cut or not cut db files?
>-PÃvodnà sprÃva-
>Od: D. Richard Hipp [mailto:[EMAIL PROTECTED]
>OdoslanÃ: 15. aprÃla 2005 11:15
>Komu: sqlite-users@sqlite.org
>Predmet: Re: [sqlite] optimize table
>
>
>On Fri, 2005-04-15 at 06:1
On Fri, 2005-04-15 at 06:15 -0400, Ken & Deb Allen wrote:
> I cannot speak for SQLITE for certain, but I know with a number of
> commercial RDBMS packages the index is not used unless it contains a
> sufficient degree of distribution.
>
SQLite does not do this. It makes no effort to keep trac
dná správa-
Od: Gert Rijs [mailto:[EMAIL PROTECTED]
Odoslané: 15. apríla 2005 7:13
Komu: sqlite-users@sqlite.org
Predmet: Re: [sqlite] optimize table
msaka msaka wrote:
CREATE TABLE sale(
id INTEGER NOT NULL UNIQUE PRIMARY KEY,
bill_no integer,
bill_item_no integer,
item_name varch
te-users@sqlite.org
Predmet: Re: [sqlite] optimize table
msaka msaka wrote:
CREATE TABLE sale(
id INTEGER NOT NULL UNIQUE PRIMARY KEY,
bill_no integer,
bill_item_no integer,
item_name varchar(20),
qty integer,
amount DOUBLE,
statistic integer
)
Why don't you replace "s
TECTED]
>Odoslané: 15. apríla 2005 7:13
>Komu: sqlite-users@sqlite.org
>Predmet: Re: [sqlite] optimize table
>
>
>msaka msaka wrote:
>>
>> CREATE TABLE sale(
>>id INTEGER NOT NULL UNIQUE PRIMARY KEY,
>>bill_no integer,
>>bill_item_no i
msaka msaka wrote:
CREATE TABLE sale(
id INTEGER NOT NULL UNIQUE PRIMARY KEY,
bill_no integer,
bill_item_no integer,
item_name varchar(20),
qty integer,
amount DOUBLE,
statistic integer
)
Why don't you replace "statistic" with a timestamp formatted like
mmdd, perhaps add
hi
i create cashdesk system with sqlite 3.
my qustions are:
i insert sales into table sale:
CREATE TABLE sale(
id INTEGER NOT NULL UNIQUE PRIMARY KEY,
bill_no integer,
bill_item_no integer,
item_name varchar(20),
qty integer,
amount DOUBLE,
statistic integer
)
table with
23 matches
Mail list logo