Hi I've found SQLite faster than MySQL and Postgres for small/medium databases. Now I have big ones and I really do not want to change, but I have some performance issues.
Consider the following database schema: CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER, word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3, word4)); CREATE INDEX tet_b ON tetragrams (word2); CREATE INDEX tet_c ON tetragrams (word3); CREATE INDEX tet_d ON tetragrams (word4); And the following database size: sqlite> SELECT COUNT(*) from tetragrams; 18397532 Now, a query like SELECT FROM tetragrams WHERE word1 = 6; returns 166579 rows; This query takes some time, but starts as soon as I type the query. The problem is that I want to use: SELECT * FROM tetragrams WHERE word1=6 ORDER BY occs DESC LIMIT 10; and it takes.. five minutes and did not give the result yet... Is there anything I can do to make it speed up CONSIDERABLY? I mean, this is to be used in a CGI and each CGI query will make 11 queries like the one above to the database. Thank you in advance, Alberto -- Alberto Simões ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
