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?