Re: [sqlite] Determine if an index has been created

On 31 Jan 2013, at 10:57pm, Igor Tandetnik wrote: > On 1/31/2013 5:45 PM, Paul Sanderson wrote: >> Is it possible to ascertain if an index on a particular column has already >> been created. > > PRAGMA index_list(YourTable), then for each index, PRAGMA >

Re: [sqlite] Determine if an index has been created

On 1/31/2013 5:45 PM, Paul Sanderson wrote: Is it possible to ascertain if an index on a particular column has already been created. PRAGMA index_list(YourTable), then for each index, PRAGMA index_info(IndexName) -- Igor Tandetnik ___ sqlite-users

Re: [sqlite] (no subject)

This makes a huge difference in speed thanks >>Also, you can write the query a bit more compactly: >>select fileref from rtable where vsc > 0 and isgraphic = 1 and md5 not in >>(select md5 from rtable where isgraphic = 1 and vsc = 0); On 31 January 2013 19:54, Igor Tandetnik

Re: [sqlite] SQL query

Actually... with that requirement, I wonder if it's even easier/better to use: Select name, min(setid), hash >From rtable Group by name, hash Having min(setid) > 0 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul

Re: [sqlite] SQL query

Add a group by name, hash and change the select to be name, min(setid), hash? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Thursday, January 31, 2013 4:48 PM To: General Discussion of SQLite Database

Re: [sqlite] SQL query

Still playing with this I have the following table and I run the following query - the results of which are what I expect name, num, md5 sqlite> select * from rtable; $RmMetadata|0|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 $RmMetadata|1|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279

Re: [sqlite] FTS4 languageid : not sure I understand this correctly

thanks for this link. Most of these tools I tested in the past, but I was hoping there was an easier path. The best langID tool is not in this list, by the way. that is: https://code.google.com/p/chromium-compact-language-detector/ gert 2013/1/31 Petite Abeille > > On

Re: [sqlite] (no subject)

On 31 Jan 2013, at 8:58pm, Simon Slavin wrote: > Depending on how chunky the values are in each column, a good index for this > would be an index on (md5,isgraphic,vsc). Sorry, that should be one index on (isgraphic,vsc), I think. Simon.

Re: [sqlite] (no subject)

On 31 Jan 2013, at 8:48pm, Paul Sanderson wrote: > Thanks all > > All columns in the query are indexed. That does not do you much good. Each SELECT can use only one index at a time. So if you have one index per column the query uses an index on, say,

Re: [sqlite] FTS4 languageid : not sure I understand this correctly

On Jan 31, 2013, at 9:27 PM, Gert Van Assche wrote: > Thanks Michael. Not what I hoped for but now I understand it. Perhaps of interest: Language Identification Tools http://www.let.rug.nl/~vannoord/TextCat/competitors.html ___

Re: [sqlite] FTS4 languageid : not sure I understand this correctly

Thanks Michael. Not what I hoped for but now I understand it. 2013/1/31 Michael Black > According to the docs: > http://www.sqlite.org/fts3.html#section_6_3 > > It's YOUR choice as to what to put in there. A separate index is created > for each language id. > So it's

[sqlite] FTS4 languageid : not sure I understand this correctly

All, I have the feeling this is the most stupid question ever, but... If I create a FTS4 table, put text in it, could I use the languageid to figure out what Language that text actually is? Is that how langID works? I did some tests, but the LangID seems to be 0 all the time, so or I'm doing

Re: [sqlite] (no subject)

On 1/31/2013 2:33 PM, Paul Sanderson wrote: My query is select fileref from rtable as r where vsc > 0 and isgraphic = 1 and not exists (select md5 fr om rtable as r1 where r.md5 = r1.md5 and isgraphic = 1 and vsc = 0); explain query plan and explain have been run on the table with the results

Re: [sqlite] (no subject)

On 31 Jan 2013, at 7:33pm, Paul Sanderson wrote: > explain query plan and explain have been run on the table with the results > below. Any ideas where and how I can improve performance? Can you show us the indexes you've created on rtable ? Simon.

Re: [sqlite] Unable to use table alias inside parenthesis on right side of join

Looks like exactly the same thing, yes. No reply, unfortunately... On 31/01/2013 1:05 PM, Kevin Benson wrote: I wonder if this earlier mention is related? http://osdir.com/ml/sqlite-users/2012-07/msg00054.html -- -- -- --Ô¿Ô-- K e V i N On Thu, Jan 31, 2013 at

Re: [sqlite] Unable to use table alias inside parenthesis on right side of join

I wonder if this earlier mention is related? http://osdir.com/ml/sqlite-users/2012-07/msg00054.html -- -- -- --Ô¿Ô-- K e V i N On Thu, Jan 31, 2013 at 12:04 PM, Ryan Johnson wrote: > Hi all, > > Strange one here... consider the following

[sqlite] Unable to use table alias inside parenthesis on right side of join

Hi all, Strange one here... consider the following schema: R(a,b) S(b,c) These queries all work fine: select R1.* from R R1 join S S1 using(b); select S1.* from R R1 join S S1 using(b); select R1.* from (R R1 join S S1 using(b)); select S1.* from (R R1 join S S1 using(b)); select R1.* from (R

[sqlite] FTS SQLite "optimize" command creates very large BLOBs in *_segment table.

Hi I have a database using SQLite-3.7.14 with a FTS4 virtual table (Free Text Search). The FTS table contains several millions of small documents. The FTS DB is created on a server (where creating time does not matter) and then used on an embedded device as a read-only database for FTS queries