SQLite Forum

Slow query
Login
I have a table in an **SQLite** database in the following form (6 columns): 

| Timestamp| fst_field  || ...         ||fifth_field|
| -------- | -----------||-----||-----------|
| 10001    | X       ||...  ||...        |
| 10003   | Y        ||...  ||...||
| 10005      | Z        ||...  ||...|
| 10020| W ||...||...

It doesn't have any primary key and all the fields are of type text except for the Timestamp (int)

The following query: 

    SELECT s.Timestamp, s.fst_field , ..., s.fifth_field, p.Timestamp, p.fst_field, ..., p.fifth_field
    FROM table s JOIN table p ON s.Timestamp < p.Timestamp AND p.Timestamp - s.Timestamp < 10

should output every row next to all its following rows within a threshold (here 10). 


The output looks like the following (12 columns):



|s.Timestamp|s.fst_field|...|s.fifth_field|p.Timestamp|p.fst_field|...|p.fifth_field|
| -------- | -----------|-----|-----------|------|------|-----|------
| 10001   | X        |...  |...        |10003|Y|...|
| 10001   | X        |...  |...        |10005|Z|...|
| ...      | ...        |...  |...        ||||


The query works for tables with 4000 rows. However, when I try it with tables with 22 million rows (1,4 GB file) it keeps running for days without ever stopping. I tried adding an index on the attribute 'Timestamp', to make the query faster but that didn't help. I ran the code both on Linux and Windows.
 
Any idea why this doesn't work?