Re: [sqlite] ORDER BY Performance on 30,000 records

[D. Richard Hipp]

On Dec 2, 2007, at 12:01 PM, Ofir Neuman wrote:

Hi All,

I have some performance problem when adding ORDER BY to my query, hope you

can help me speed things up.

This is my table:

TABLE1
{
  ID TEXT
  ParentID TEXT
  ModifiedDate INTEGER
}

ID is the PK of the table and i also have an index on ParentID.

Drop the index on ParentID and replace it with this:

   CREATE INDEX idx2 ON table1(ParentID, ModifiedDate, ID);

Then queries of the form

   SELECT id FROM table1 WHERE parentid=? ORDER BY modifieddate;

will be very fast.

Current number of records in table: 40,000

My query is very simple:

SELECT ID FROM Table1 WHERE ParentID = '{FFFF-FFFF-FFFF-FFFF}' ORDER BY

ModifiedDate

According to the data in my table this query should return 30,000 records.

While using ORDER BY it takes 3-4 SEC to retrieve the query result, without

the ORDER BY it take something like 30 ms.

Tried to index also 'ModifiedDate' but it didn't help.

What am i doing wrong?

Thanks,
Ofir Neuman.

D. Richard Hipp
[EMAIL PROTECTED]




-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------