Hello,
i'm having strange performance problem with sqlite-3.2.0. consider the
following table
sqlite> .schema data
CREATE TABLE data
(
a INTEGER,
b INTEGER,
c CHAR,
d INTEGER,
e INTEGER,
n1 FLOAT,
n2 FLOAT
);
CREATE INDEX data_by_a ON data (a);
sqlite> select count(*) from data;
9818210
sqlite> select count(*) from data where a <= 18234721;
333392
now the problem:
1) if i do a select with an idex it takes 27 sec. to get 333392 rows
> time sqlite3 db 'select n2 from data where a <= 18234721' > /dev/null
25.95u 0.71s 0:27.02 98.6%
> time sqlite3 db 'select n2 from data where a <= 18234721' > /dev/null
26.02u 0.66s 0:27.53 96.9%
i run the query twice to reduce effect of disk/filesystem cache
2) if i do a select with sequential lookup it takes 1min to get 9818210 rows
> time sqlite3 db 'select n2 from data where a+0 <= 18234721' > /dev/null
49.54u 14.65s 1:04.98 98.7%
> time sqlite3 db 'select n2 from data where a+0 <= 18234721' > /dev/null
49.80u 14.42s 1:05.03 98.7%
- so how come it take only twice as much time to get 30 times more rows?
- and why is it taking 27 seconds to get 333392 rows anyway?
- is there any way to create an index in separate file?
the hardware is sun netra t1 running solaris 5.7. the db file size is
about 800 mbytes.
just for the record i'd like to have at least 15 times more records in
the 'data' table.
thanks,
max
