On 4/03/2009 2:48 PM, Peng Huang wrote: > On Wed, Mar 4, 2009 at 11:40 AM, John Machin <sjmac...@lexicon.net> wrote: > >> On 4/03/2009 2:12 PM, Peng Huang wrote: >>> 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" >>> >> (y3 = ? or y3 = ? or y3 = ?) >> is *logically* equivalent to >> (y3 IN (?, ?, ?)) >> >> Does it run at the same speed? > > > Its improvement is not obviously. :(
OK, next try: Input is 1 to 4 pinyin characters ... with tone or without tone? Even with tone, (number of different pinyin characters (a few hundred, about 410 IIRC)) * 5 tones will fit easily in 16 bits. So a possible pronunciation of a 4-character phrase will easily fit in a 64-bit number. So instead of complications with y0, y1, y2, y3 just use one composite number e.g. (((y0 * 10000 + y1) * 10000) + y2 * 10000) + y3 -- note that for phrases shorter tan 4 characters, you would need a special code (zero) for "no character at all". Instead of complicated logic to generate varing sizes and shapes of <query1> union all <query 2> ...., you just have to produce a list of the possible composite keys and select * from the_table where composite_key in (?, ?, ?,...) I'd be interested to get Igor's thoughts on how much speed up might be achievable by doing this. Another thought: if you have enough memory (is this for a PC or a phone?), an in-memory hash-table might be a good idea. HTH, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
