Re: [sqlite] SQL query causes various hangs/crashes/exits

One of the things I try to maintain when writing databases is that whenever I start poking at a field with WHERE, said field gets an index to its own, and then I leave it to the engine of choice (SQLite, MSSql, MySQL) to decide to use the indexes. I have rarely been let down performance wise.

Re: [sqlite] SQL query causes various hangs/crashes/exits

Hello... On 27/1/2012 3:20 AM, Larry Knibb wrote: On 25 January 2012 21:01, Igor Tandetnik wrote: This index can help satisfy conditions of the form (traditional='X') or (traditional='X' AND simplified='Y'). But it doesn't help at all for conditions on (simplified='Y')

Re: [sqlite] SQL query causes various hangs/crashes/exits

Thanks! Splitting the index into two did the trick and now the query is working well. Cheers, Larry On 25 January 2012 21:01, Igor Tandetnik wrote: > Larry Knibb wrote: >> SELECT DISTINCT d.rowid AS id, d.*, i.relevance >> FROM dictionary d >> JOIN

Re: [sqlite] SQL query causes various hangs/crashes/exits

On 25 Jan 2012, at 1:01pm, Igor Tandetnik wrote: > So your query devolves to a full table scan, and apparently, that just takes > a long time. That's my guess: the query is taking a long time. It's taking long enough that your database infrastructure (which seems to be SQLite Database

Re: [sqlite] SQL query causes various hangs/crashes/exits

Larry Knibb wrote: > SELECT DISTINCT d.rowid AS id, d.*, i.relevance > FROM dictionary d > JOIN hp_index i ON i.dictionary_id = d.rowid > JOIN hanzi h ON h.rowid = i.hanzi_id > WHERE h.traditional = '我' OR h.simplified = '我' > ORDER BY i.relevance desc > > I can get it to

[sqlite] SQL query causes various hangs/crashes/exits

Hi, I hope I'm in the right place - this is my first post to sqlite-users. I'm looking for help on a query I am composing which seems to be blowing-up the query engine/API. Or maybe I'm just missing something very obvious... I have a query which works fine on a small database (15KB) which I