Hi,
I am facing a peculiar issue with SQLITE.
The following is my table structure :
CREATE TABLE map (
n BIGINT NOT NULL DEFAULT 0,
s INT(5) NOT NULL DEFAULT 0,
d INT(5) NOT NULL DEFAULT 0,
c INT(1) NOT NULL DEFAULT 0,
b UNSIGNED BIGINT NOT NULL DEFAULT 0
);
CREATE INDEX map_index ON map (n, s, d, c, b);
The above table is having nearly 600 Million Records and is of size 26 GB.
The column 'n' is representing Numbers of Blocks on the file system.
's' stands for Snapshot ID.
'd' is device id
'c' is not used and contains 0 all the time.
'b' is Block ID which is in another table altogether.
Now I need to retrieve the block numbers in order for lets say d = 15 and s
<= 326.
The device 15 has nearly 100000 entries in the table while the remaining of
the 600 Million records belong to another device.
I retrieve the blocks in max limits of 32768 and my last query in the loop
is :
SELECT n, c, b, s FROM map WHERE s <= '326' AND s NOT IN (0) AND d = '15'
AND n >= '15591116' ORDER BY n ASC LIMIT 0, 32768
The query is executed immediately and it then starts to show the result and
the last rows are :
15731619|0|13359834|2
15731620|0|13359835|2
15731621|0|13359836|2
15731622|0|13359837|2
15731623|0|13359838|2
15731624|0|13359839|2
15731625|0|13359840|2
15731626|0|13359841|2
15731627|0|13359842|2
15731628|0|13359843|2
15731629|0|13359844|2
15731630|0|13359845|2
15731631|0|13359846|2
15731632|0|13359847|2
15731633|0|13359848|2
15731634|0|13359849|2
15731635|0|13359850|2
15731636|0|13359851|2
15731637|0|13359852|2
15731638|0|13359853|2
15731639|0|13359854|2
After this SQLITE goes into an endless search for some reasons unknown. It
is doing something in the background (my guess its trying to search - I did
an strace) and keeps on doing it for nearly 10-15 minutes before it stops
and doesnt even give any new row after the above results.
The CPU shoots up during this and the following some of the strace (its
actually very large and I am just showing some results which show block
reads) :
lseek(3, 15185012736, SEEK_SET) = 15185012736
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185013760, SEEK_SET) = 15185013760
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185014784, SEEK_SET) = 15185014784
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185015808, SEEK_SET) = 15185015808
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185016832, SEEK_SET) = 15185016832
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185017856, SEEK_SET) = 15185017856
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
Now, I dont understand why is this happening because I do have a full
fledged index for the entire table.
Also no new results are given after the whole search fiasco and the number
of results for this above query is 1568 rows.
Any ideas as to why this would be occuring.
I tried this similar query for the device which has nearly 500 Million
results and the last results of that query is pretty fast as usual.
Its for the smaller devices (e.g. with ID 15 above) the issue arises.
Any input will be appreciated.
Regards,
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
