On Tue, 2007-04-17 at 11:53 +0100, Alberto Simões wrote: > 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.
You might need an index like: CREATE INDEX tet_e ON tetragrams(word1, occs); Otherwise you have to sort the 166000 items each time the query is made. Dan. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
