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
