On Sat, 19 Oct 2013 21:21:44 +0530
Raheel Gupta <raheel...@gmail.com> wrote:
> 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.
First, consider if some combination of those columns constitute a
primary key. That would be stronger than a simple index.
Second, if you're searching for n based on d and s, don't put n at the
front of the index.
create /* covering */ index ByDS on map(d, s, n);
and watch SQLite fetch your rows in O(log2 n) instead of O(n).
Third, an unused column promotes confusion and inefficiency. If you
need a column later, you can add a column later.
Fourth (if I may), "map" is an unlovely name for a table. Every row in
any table is a map of key to value. You might as well call it map_table
(and you wouldn't be the first). I suspect "device_blocks" might be a
better name. Perhaps
CREATE TABLE devices (
snapshot integer NOT NULL,
device integer NOT NULL,
block integer NOT NULL check (block >= 0),
nblocks integer NOT NULL,
primary key(snapshot, device, block)
);
gives you a table that says what it is, prevents duplication, and lets
you easily fetch block counts by snapshot and device.
HTH.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
