"Peng Huang" <shawn.p.hu...@gmail.com> wrote in message news:fb7465350903031832w20777903kc5bb1f641f455...@mail.gmail.com > But we need add some new requirements. We need query phrases by > length of phrases (ylen) and pronounce of each characters in phrase, > but the pronounce of one or two characters may be two different > integer. The SQL may like: "SELECT * FROM py_phrase WHERE ylen = ? > and (y0 = ? or y0 = ?) and y1 = ? and (y2 = ? or y2 = ?) ORDERY BY > user_freq DESC, freq DESC". > If I use "OR" operator in WHERE statement, the SQL performance will > be not acceptable. It is very slow.
select * from py_phrase where rowid in ( select rowid from py_phrase where ylen=:ylen and y0=:y0_1 and y1=:y1 and y2=:y2_1 union all select rowid from py_phrase where ylen=:ylen and y0=:y0_1 and y1=:y1 and y2=:y2_2 union all select rowid from py_phrase where ylen=:ylen and y0=:y0_2 and y1=:y1 and y2=:y2_1 union all select rowid from py_phrase where ylen=:ylen and y0=:y0_2 and y1=:y1 and y2=:y2_2 ) ORDERY BY user_freq DESC, freq DESC; Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
