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.
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.
You are not doing anything wrong, it just takes time to sort the result
set since you have no index on the sort key. When you indexed the sort
column you hit an Sqlite restriction that it only uses one index on a query.
You might experiment by using an index combining ParentId and ModifiedDate.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
