SQLite3 simple index tests and a bit worrying results

Post date: Feb 12, 2016 1:53:07 PM

I did some SQLite3 indexing tests and got quite interesting yet not actually very surprising results.

Test setup two int columns and one first with low cardinality and second with high cardinality.

10 million ~1 KB rows.

Test timing:

1. Without index 1045 seconds

2. With column 1 index 50 seconds

3. With column 2 index: 0.12 seconds

4. With column 1 and 2 indexed : 46 seconds

5. With column 1 and 2 composite index: 0.17 seconds

I did run several test queries with higher and lower number of queries being done, but that actually didn't change a thing, time required relative to other tests. So times do not matter, only rations between those queries.

Only thing which actually baffles me is that why 1 and 2 indexed gives so poor result (test 4). It means just one thing, index selection failed very badly.

Google+ discussion about this post.

Related explain dumps:

1. ['explain select * from test where k1 = ? and k2 = ?;', (3199, 6873199)]

[(0, 'Trace', 0, 0, 0, '', '00', None), (1, 'Variable', 1, 1, 0, '', '00', None), (2, 'Variable', 2, 2, 0, '', '00', None), (3, 'Goto', 0, 17, 0, '', '00', None), (4, 'OpenRead', 0, 2, 0, '3', '00', None), (5, 'Rewind', 0, 15, 0, '', '00', None), (6, 'Column', 0, 0, 3, '', '00', None), (7, 'Ne', 1, 14, 3, 'collseq(BINARY)', '6a', None), (8, 'Column', 0, 1, 4, '', '00', None), (9, 'Ne', 2, 14, 4, 'collseq(BINARY)', '6a', None), (10, 'Column', 0, 0, 6, '', '00', None), (11, 'Column', 0, 1, 7, '', '00', None), (12, 'Column', 0, 2, 8, '', '00', None), (13, 'ResultRow', 6, 3, 0, '', '00', None), (14, 'Next', 0, 6, 0, '', '01', None), (15, 'Close', 0, 0, 0, '', '00', None), (16, 'Halt', 0, 0, 0, '', '00', None), (17, 'Transaction', 0, 0, 0, '', '00', None), (18, 'VerifyCookie', 0, 75, 0, '', '00', None), (19, 'TableLock', 0, 2, 0, 'test', '00', None), (20, 'Goto', 0, 4, 0, '', '00', None)]

2. ['explain select * from test where k1 = ? and k2 = ?;', (5909, 6835909)]

[(0, 'Trace', 0, 0, 0, '', '00', None), (1, 'Variable', 1, 1, 0, '', '00', None), (2, 'Variable', 2, 2, 0, '', '00', None), (3, 'Goto', 0, 21, 0, '', '00', None), (4, 'OpenRead', 0, 2, 0, '3', '00', None), (5, 'OpenRead', 1, 11269773, 0, 'keyinfo(1,BINARY)', '00', None), (6, 'IsNull', 1, 18, 0, '', '00', None), (7, 'SeekGe', 1, 18, 1, '1', '00', None), (8, 'IdxGE', 1, 18, 1, '1', '01', None), (9, 'IdxRowid', 1, 3, 0, '', '00', None), (10, 'Seek', 0, 3, 0, '', '00', None), (11, 'Column', 0, 1, 4, '', '00', None), (12, 'Ne', 2, 17, 4, 'collseq(BINARY)', '6a', None), (13, 'Column', 1, 0, 6, '', '00', None), (14, 'Column', 0, 1, 7, '', '00', None), (15, 'Column', 0, 2, 8, '', '00', None), (16, 'ResultRow', 6, 3, 0, '', '00', None), (17, 'Next', 1, 8, 0, '', '00', None), (18, 'Close', 0, 0, 0, '', '00', None), (19, 'Close', 1, 0, 0, '', '00', None), (20, 'Halt', 0, 0, 0, '', '00', None), (21, 'Transaction', 0, 0, 0, '', '00', None), (22, 'VerifyCookie', 0, 76, 0, '', '00', None), (23, 'TableLock', 0, 2, 0, 'test', '00', None), (24, 'Goto', 0, 4, 0, '', '00', None)]

3. ['explain select * from test where k1 = ? and k2 = ?;', (9281, 3559281)]

[(0, 'Trace', 0, 0, 0, '', '00', None), (1, 'Variable', 1, 1, 0, '', '00', None), (2, 'Variable', 2, 2, 0, '', '00', None), (3, 'Goto', 0, 21, 0, '', '00', None), (4, 'OpenRead', 0, 2, 0, '3', '00', None), (5, 'OpenRead', 1, 11269773, 0, 'keyinfo(1,BINARY)', '00', None), (6, 'IsNull', 2, 18, 0, '', '00', None), (7, 'SeekGe', 1, 18, 2, '1', '00', None), (8, 'IdxGE', 1, 18, 2, '1', '01', None), (9, 'IdxRowid', 1, 3, 0, '', '00', None), (10, 'Seek', 0, 3, 0, '', '00', None), (11, 'Column', 0, 0, 4, '', '00', None), (12, 'Ne', 1, 17, 4, 'collseq(BINARY)', '6a', None), (13, 'Column', 0, 0, 6, '', '00', None), (14, 'Column', 1, 0, 7, '', '00', None), (15, 'Column', 0, 2, 8, '', '00', None), (16, 'ResultRow', 6, 3, 0, '', '00', None), (17, 'Next', 1, 8, 0, '', '00', None), (18, 'Close', 0, 0, 0, '', '00', None), (19, 'Close', 1, 0, 0, '', '00', None), (20, 'Halt', 0, 0, 0, '', '00', None), (21, 'Transaction', 0, 0, 0, '', '00', None), (22, 'VerifyCookie', 0, 78, 0, '', '00', None), (23, 'TableLock', 0, 2, 0, 'test', '00', None), (24, 'Goto', 0, 4, 0, '', '00', None)]

4. ['explain select * from test where k1 = ? and k2 = ?;', (2623, 622623)]

[(0, 'Trace', 0, 0, 0, '', '00', None), (1, 'Variable', 1, 1, 0, '', '00', None), (2, 'Variable', 2, 2, 0, '', '00', None), (3, 'Goto', 0, 21, 0, '', '00', None), (4, 'OpenRead', 0, 2, 0, '3', '00', None), (5, 'OpenRead', 1, 11385083, 0, 'keyinfo(1,BINARY)', '00', None), (6, 'IsNull', 1, 18, 0, '', '00', None), (7, 'SeekGe', 1, 18, 1, '1', '00', None), (8, 'IdxGE', 1, 18, 1, '1', '01', None), (9, 'IdxRowid', 1, 3, 0, '', '00', None), (10, 'Seek', 0, 3, 0, '', '00', None), (11, 'Column', 0, 1, 4, '', '00', None), (12, 'Ne', 2, 17, 4, 'collseq(BINARY)', '6a', None), (13, 'Column', 1, 0, 6, '', '00', None), (14, 'Column', 0, 1, 7, '', '00', None), (15, 'Column', 0, 2, 8, '', '00', None), (16, 'ResultRow', 6, 3, 0, '', '00', None), (17, 'Next', 1, 8, 0, '', '00', None), (18, 'Close', 0, 0, 0, '', '00', None), (19, 'Close', 1, 0, 0, '', '00', None), (20, 'Halt', 0, 0, 0, '', '00', None), (21, 'Transaction', 0, 0, 0, '', '00', None), (22, 'VerifyCookie', 0, 79, 0, '', '00', None), (23, 'TableLock', 0, 2, 0, 'test', '00', None), (24, 'Goto', 0, 4, 0, '', '00', None)]

5. ['explain select * from test where k1 = ? and k2 = ?;', (4783, 4964783)]

[(0, 'Trace', 0, 0, 0, '', '00', None), (1, 'Variable', 1, 1, 0, '', '00', None), (2, 'Variable', 2, 2, 0, '', '00', None), (3, 'Goto', 0, 22, 0, '', '00', None), (4, 'OpenRead', 0, 2, 0, '3', '00', None), (5, 'OpenRead', 1, 11385083, 0, 'keyinfo(2,BINARY,BINARY)', '00', None), (6, 'SCopy', 1, 3, 0, '', '00', None), (7, 'IsNull', 3, 19, 0, '', '00', None), (8, 'SCopy', 2, 4, 0, '', '00', None), (9, 'IsNull', 4, 19, 0, '', '00', None), (10, 'SeekGe', 1, 19, 3, '2', '00', None), (11, 'IdxGE', 1, 19, 3, '2', '01', None), (12, 'IdxRowid', 1, 5, 0, '', '00', None), (13, 'Seek', 0, 5, 0, '', '00', None), (14, 'Column', 1, 0, 6, '', '00', None), (15, 'Column', 1, 1, 7, '', '00', None), (16, 'Column', 0, 2, 8, '', '00', None), (17, 'ResultRow', 6, 3, 0, '', '00', None), (18, 'Next', 1, 11, 0, '', '00', None), (19, 'Close', 0, 0, 0, '', '00', None), (20, 'Close', 1, 0, 0, '', '00', None), (21, 'Halt', 0, 0, 0, '', '00', None), (22, 'Transaction', 0, 0, 0, '', '00', None), (23, 'VerifyCookie', 0, 82, 0, '', '00', None), (24, 'TableLock', 0, 2, 0, 'test', '00', None), (25, 'Goto', 0, 4, 0, '', '00', None)]