Hi SQLite hackers,
I am using sqlite in a Chinese input method. The db schema is below, and the
table contains about 1000000 records.
CREATE TABLE py_phrase (
ylen INTEGER, /* length of phrase. How many characters in phrase */
y0 INTEGER, y1 INTEGER, y2 INTEGER, y3 INTEGER, /* pronounce of
characters in phrases. y0: first characters, y1 is the second. and ... */
s0 INTEGER, s1 INTEGER, s2 INTEGER, s3 INTEGER,
phrase TEXT, /* phrase */
freq INTEGER, /* frequency */
user_freq INTEGER /* user frequency */
);
I create INDEX like:
CREATE INDEX py_phrase_index_2 ON py_phrase (ylen, y0, y1, y2, y3);
CREATE INDEX py_phrase_index_3 ON py_phrase (ylen, s0, s1, s2, s3);
In the input method, we need query phrases by length of phrases (ylen) and
pronounce of each characters in phrase.
My SQL query statements is like:
"SELECT * FROM py_phrase WHERE ylen = ? and y0 = ? and y1 = ? and y2 = ?
ORDERY BY user_freq DESC, freq DESC"
The performance is good.
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.
Anybody can give me some suggestions to improve the performance of my SQLite
database?
Thanks.
Peng Huang
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
