SQLite database optimizations for WigleWifi

[thwiggle]

Hi All,

In the past I’ve had some performance problems with WigleWifi before version 1.16; In dense undiscovered areas the queue was increasing to 300+ access points, which resulted in highly delayed ‘pings’.

Since I upgraded to WigleWifi version 1.16 I didn’t remark any ‘queue’ messages anymore on my earphones, to my surprise this is archived by an application improvement rather than due a database optimization.

Maybe a little bit technical, but before the ‘queue’ issue I’ve had almost added two indexes to the WigleWifi for Android Database:

CREATE INDEX network_bssid_index ON network(bssid);
CREATE INDEX location_bssid_index ON location(bssid);

Although I don’t know exactly which SQL queries WigleWifi is using I think these indexes will also guarantee performance in the future with larger mobile databases.

Only one disadvantage is that above indexes require some extra amount of storage, in a test case with my wiglewifi.sqlite database (100K access points and 500K locations) my database increased 73% in size.

[bobzilla]

bssid is the primary key in the network table, and is automatically indexed by sqlite. WigleWifi only uses the primary keys in the tables, as indexing does make a huge difference in any db environment.