Re: [sqlite] faster query - help

2013-01-25 Thread Igor Tandetnik
On 1/25/2013 9:45 AM, moti lahiani wrote: Hi I have a data base with the following tables 1) files - each file in the system have his information in this table. that table include 12 different columns like file_id as integer primary key autoincrement, date_uploaded as integer, file_type as

Re: [sqlite] Substring Search Across Many Columns

2013-01-25 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 25/01/13 12:59, Paul Vercellotti wrote: > As I understand, it's tricky to get FTS to do substring matching, no? > What's the best way to do that? In what way is it tricky? There are several examples of doing it in the doc I pointed to. Even when

Re: [sqlite] Substring Search Across Many Columns

2013-01-25 Thread Paul Vercellotti
As I understand, it's tricky to get FTS to do substring matching, no?  What's the best way to do that? Thanks! Paul From: Roger Binns To: General Discussion of SQLite Database Sent: Friday, January 25, 2013

[sqlite] identify virtual tables?

2013-01-25 Thread Petite Abeille
Hello, What would be a reasonable way to programmatically identify all the virtual tables in a database? Is there a structured way to do so? Short of scanning the DDLs that is? selectname from sqlite_master where type = 'table' and lower( sql ) like '% virtual %' order by

Re: [sqlite] Substring Search Across Many Columns

2013-01-25 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 25/01/13 11:30, Paul Vercellotti wrote: > I'm trying to match a substring (case-insensitive) across multiple > columns and want it to go fast; it's very slow to do a query like '... > WHERE name LIKE "%fish%" OR desc LIKE "%red%" OR title LIKE >

[sqlite] Substring Search Across Many Columns

2013-01-25 Thread Paul Vercellotti
Hi there, I'm trying to match a substring (case-insensitive) across multiple columns and want it to go fast; it's very slow to do a query like '... WHERE name LIKE "%fish%" OR desc LIKE "%red%" OR title LIKE "%soup%"...'  Will creating a composite index that has all those columns speed up a

Re: [sqlite] using the same sqlite parameter more than once causes premature memory deallocation

2013-01-25 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 24/01/13 17:37, abbood wrote: > Btw I'm curious how did you find out about this auto release thing? Can > you add more detail about that? I used a debugger to set a breakpoint in malloc_error_debug as the message says. That gave a stack trace of

Re: [sqlite] faster query - help

2013-01-25 Thread Michael Black
The real answer is try both and see which is faster. My guess is #1 is probably faster since I don't think there's an easy way to limit the left-hand side of a left-join operation to do #2 without touching all the music_file records, is there? The join would have to match on file_id so would hit

Re: [sqlite] bug report: out of memory error for most operations on a large database

2013-01-25 Thread Michael Black
How much free disk space do you have? Your temp tables might be exceeding capacity. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ammon_lymphater Sent: Thursday, January 24, 2013 1:41 PM To: sqlite-users@sqlite.org Subject:

[sqlite] bug report: out of memory error for most operations on a large database

2013-01-25 Thread ammon_lymphater
Summary: except for select count(*) , all operations on a large table (500m rows/80gb) result in out-of-memory error Details: a. Using SQLite3 ver 3.7.15.2, Windows 7 64 bit AMD(but the error also in 3.6.xx and on other Windows platforms) b. Created a table (schema attached),