On 4/17/07, Alberto Simões <[EMAIL PROTECTED]> wrote:
On 4/17/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > "=?ISO-8859-1?Q?Alberto_Sim=F5es?=" <[EMAIL PROTECTED]> wrote: > > > > 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); > > > > 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? > > CREATE INDEX tet_e ON tetragrams(word1, occs);
I tried: CREATE INDEX tet_a ON tetragrams(word1, occs); CREATE INDEX tet_b ON tetragrams(word2, occs); CREATE INDEX tet_c ON tetragrams(word3, occs); CREATE INDEX tet_d ON tetragrams(word4, occs); and PRIMARY KEY(word1,word2,word3,word4) This works fairly well if I need SELECT * from tetragrams WHERE word1 = 'x' ORDER BY occs; The problem is that I need as well SELECT * from tetragrams WHERE word1 = 'x' AND word2 = 'y' ORDER BY occs; and this is QUITE slow. and more (try 16 combinations). That means I need 16 indexes? :-S Just to let you have an idea, this schema: CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER, word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3, word4)); CREATE INDEX tet_a ON tetragrams (word1,occs); CREATE INDEX tet_b ON tetragrams (word2,occs); CREATE INDEX tet_c ON tetragrams (word3,occs); CREATE INDEX tet_d ON tetragrams (word4,occs); is already with 1.8GB of disk :) TIA Alberto -- Alberto Simões ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
