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 <itandet...@mvps.org> wrote: > Larry Knibb <larry.kn...@gmail.com> 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 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); > > 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') or (traditional='X' OR simplified='Y'). So > your query devolves to a full table scan, and apparently, that just takes a > long time. > > Think about it this way. Imagine you have a phonebook with people's names, > sorted by last name then first name. This makes it easy to find all Smiths, > or all John Smiths, but doesn't help with the task of finding all Johns, nor > all people who are either John or Smith. > > You want two separate indexes, one on hanzi(traditional) and another on > hanzi(simplified) > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
