all of the below is really good advice that I shall follow over this weekend.
Many thanks.
On Sep 29, 2011, at 10:05 AM, Petite Abeille wrote:
>
> On Sep 29, 2011, at 3:30 PM, Mr. Puneet Kishor wrote:
>
>> Well, defeated by FTS4 for now, I will try the following approach --
>
> [didn't
On Sep 29, 2011, at 3:30 PM, Mr. Puneet Kishor wrote:
> Well, defeated by FTS4 for now, I will try the following approach --
[didn't follow the thread blow by blow, so apologies if this was already
covered and dismissed :)]
Before you jump to the deep end...
FTS tables are meant to be
.org [sqlite-users-boun...@sqlite.org] on
> behalf of Mr. Puneet Kishor [punk.k...@gmail.com]
> Sent: Thursday, September 29, 2011 8:30 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] speeding up FTS4
>
>
> Well, defeated by FTS4 for now, I will try
On 29 Sep 2011, at 2:30pm, Mr. Puneet Kishor wrote:
> Well, defeated by FTS4 for now, I will try the following approach --
>
> 1. drop the fts tables and rebuild them and test.
>
> 2. if the above doesn't work, then either migrate the data to Postgres and
> use its fts, or implement e-Swish
Database
Subject: EXT :Re: [sqlite] speeding up FTS4
Well, defeated by FTS4 for now, I will try the following approach --
1. drop the fts tables and rebuild them and test.
2. if the above doesn't work, then either migrate the data to Postgres and use
its fts, or implement e-Swish or httpdig
Well, defeated by FTS4 for now, I will try the following approach --
1. drop the fts tables and rebuild them and test.
2. if the above doesn't work, then either migrate the data to Postgres and use
its fts, or implement e-Swish or httpdig for full text search.
On Sep 28, 2011, at 4:35 PM,
On Sep 28, 2011, at 4:18 PM, Black, Michael (IS) wrote:
> I have no idea if this would work...but...here's some more thoughts...
>
>
>
> #1 How long does this take:
>
> select count(*) from fts_uri match 'education school';
>
>
>
> #2 Create a view on uris with just what you need and
Subject: EXT :Re: [sqlite] speeding up FTS4
On Sep 28, 2011, at 2:41 PM, Black, Michael (IS) wrote:
> What happens if you create an index on uris(feed_history_id)
>
>
>
Yeah, I noticed that lacking as well.
sqlite> EXPLAIN QUERY PLAN SELECT u.uri_id
...> FROM projects p
.
On 09/28/11 21:55, Puneet Kishor wrote:
Perhaps, but I have inserted that in my table where the column is INTEGER.
sqlite> SELECT typeof(u_downloaded_on) FROM uris LIMIT 1;
integer
--
OK!
___
sqlite-users mailing list
sqlite-users@sqlite.org
On Sep 28, 2011, at 2:26 PM, Roger Andersson wrote:
> On 09/28/11 21:10, Black, Michael (IS) wrote:
>>
>> 'scuse meI was wrong (again)...I guess strftime does return an
>> integerseems to me that belies the name as it's a mismatch to the unix
>> function.
>>
>>
> ?
> SQLite version
__
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Puneet Kishor [punk.k...@gmail.com]
> Sent: Wednesday, September 28, 2011 2:00 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] speeding up FTS4
>
[punk.k...@gmail.com]
Sent: Wednesday, September 28, 2011 2:00 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] speeding up FTS4
On Sep 28, 2011, at 1:14 PM, Black, Michael (IS) wrote:
> strftime returns a text representation. So you didn't really change anything.
>
On Sep 28, 2011, at 9:00 PM, Puneet Kishor wrote:
> If I understand correctly, the *size* of the database should not matter. Or,
> at least not matter as much.
So she said. But contrary to popular believe, size does matter.
___
sqlite-users mailing
On 09/28/11 21:10, Black, Michael (IS) wrote:
'scuse meI was wrong (again)...I guess strftime does return an
integerseems to me that belies the name as it's a mismatch to the
unix function.
?
SQLite version 3.7.8 2011-09-19 14:49:19
Enter ".help" for instructions
Enter SQL
ion Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Roger Andersson [r...@telia.com]
Sent: Wednesday, September 28, 2011 1:52 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] speed
__
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Puneet Kishor [punk.k...@gmail.com]
> Sent: Wednesday, September 28, 2011 12:44 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite]
On 09/28/11 20:14, Black, Michael (IS) wrote:
strftime returns a text representation. So you didn't really change anything.
You need to use juliandays() as I said.
And you want a REAL number...not integer...though SQLite doesn't really care
what you call it. It's more for your own
...@gmail.com]
Sent: Wednesday, September 28, 2011 12:44 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] speeding up FTS4
On Sep 28, 2011, at 11:00 AM, Black, Michael (IS) wrote:
> Your change to numeric date/time may not take a long as you think.
>
>
>
Took an hou
On 28 Sep 2011, at 6:44pm, Puneet Kishor wrote:
> Step 4: Run the following query
>
> SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on
> FROM fts_uri f
> JOIN uris u ON f.uri_id = u.uri_id
> JOIN feed_history fh ON u.feed_history_id =
On Sep 28, 2011, at 11:00 AM, Black, Michael (IS) wrote:
> Your change to numeric date/time may not take a long as you think.
>
>
>
Took an hour and a half.
Step 1: Alter all tables with datetime columns, converting those columns to
integer;
Step 2: Update all tables setting new datetime
Your change to numeric date/time may not take a long as you think.
drop any indexes on project_start and downloaded_on;
update projects set project_start=julianday(project_start);
update uris set downloaded_on=julianday(downloaded_on);
Recreate indexes.
Modify your code to insert
Sounds like you may just be hitting disk i/o. Your "sys" numbers seem to
indicate that.
How much memory does your machine have?
How much time does each WHERE clause take?
select count(*) from project where project_id = 3;
select count(*) from fts_uri MATCH 'education,school';
select
On Sep 28, 2011, at 8:19 AM, Black, Michael (IS) wrote:
> P.S. Your projects table is missing project_start. So apparently these
> aren't the real create statements you are using.
>
>
>
>
Sorry, I think that is the only table from which I snipped off information to
make the post
P.S. Your projects table is missing project_start. So apparently these aren't
the real create statements you are using.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org
24 matches
Mail list logo
