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 manipulated to have just one matching result, but on my full-size database (40MB) this query causes my Windows Phone app to completely die with no exception and similarly on my desktop PC while trying to understand the issue SQLite Database Browser 2.0 b1.exe ends up "Not Responding" (never comes back to life). Unfortunately I can't execute this query via sqlite3.exe because it contains Unicode characters in the query string. I'm happy to provide any more background info as required but just on first sight, is there anything immediately wrong with this query? 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 work by dropping the OR h.simplified part of the WHERE clause. However, if I use a single where clause but use the h.simplified column instead then it breaks again. Maybe it's the index? CREATE INDEX i3 ON hanzi(traditional, simplified); Any help much appreciated for my first post. Cheers, Larry P.S. here's the query plan fr the first broken (on large dataset) query showing the indexes working as intended: 0|0|2|SEARCH TABLE hanzi AS h USING COVERING INDEX i3 (traditional=?) (~10 rows) 0|0|2|SEARCH TABLE hanzi AS h USING AUTOMATIC COVERING INDEX (simplified=?) (~7 rows) 0|1|1|SEARCH TABLE hp_index AS i USING INDEX i5 (hanzi_id=?) (~10 rows) 0|2|0|SEARCH TABLE dictionary AS d USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|0|0|USE TEMP B-TREE FOR DISTINCT 0|0|0|USE TEMP B-TREE FOR ORDER BY _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
