Hi Igor Tandetnik, Thanks for your quick reply.
Your solution works. But in some cases, each y%d may has two or three choices. So the SQL will become very complex, we need ( 2 * 2 * 2 * 2) sub where statements. Does SQLite have some build-in features to optimize those kinds of SQL statements? Or do you have other suggestions to optimize the database of SQL statements? For example: "SELECT * FROM py_phrase WHERE ylen = ? and (y0 = ? or y0 = ?) and (y1 = ? or y1 =? ) and (y2 = ? or y2 = ?) and (y3 = ? or y3 = ? or y3 = ?) ORDERY BY user_freq DESC, freq DESC" Thanks Peng Huang On Wed, Mar 4, 2009 at 10:49 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > "Peng Huang" <shawn.p.hu...@gmail.com> > wrote in message > news:fb7465350903031832w20777903kc5bb1f641f455...@mail.gmail.com<news%3afb7465350903031832w20777903kc5bb1f641f455...@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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
