clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799232 fiddles created (41692 in the last week).

CREATE TABLE dbsnp ( chromosome TEXT table_constraint )
 hidden batch(es)


INSERT INTO dbsnp SELECT 'TEST_1' FROM ( (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t1, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t2, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t3, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t4, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t5, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t6, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t7, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t8, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t9 )
 hidden batch(es)


INSERT INTO dbsnp SELECT 'TEST_2' FROM ( (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t1, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t2, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t3, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t4, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t5, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t6, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t7, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t8, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t9 )
 hidden batch(es)


INSERT INTO dbsnp SELECT 'TEST_3' FROM ( (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t1, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t2, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t3, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t4, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t5, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t6, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t7, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t8 )
 hidden batch(es)


SELECT COUNT(*) FROM dbsnp
COUNT(*)
4296875
 hidden batch(es)


CREATE INDEX chromosome_IX ON dbsnp (chromosome)
 hidden batch(es)


EXPLAIN QUERY PLAN SELECT DISTINCT chromosome FROM dbsnp
selectid order from detail
0 0 0 SCAN TABLE dbsnp USING COVERING INDEX chromosome_IX
 hidden batch(es)


SELECT strftime('%Y-%m-%d %H-%M-%f','now')
strftime('%Y-%m-%d %H-%M-%f','now')
2017-12-27 21-15-08.175
 hidden batch(es)


SELECT DISTINCT chromosome FROM dbsnp;
chromosome
TEST_1
TEST_2
TEST_3
 hidden batch(es)


SELECT strftime('%Y-%m-%d %H-%M-%f','now')
strftime('%Y-%m-%d %H-%M-%f','now')
2017-12-27 21-15-08.492
 hidden batch(es)


EXPLAIN QUERY PLAN SELECT MIN(chromosome) FROM dbsnp
selectid order from detail
0 0 0 SEARCH TABLE dbsnp USING COVERING INDEX chromosome_IX
 hidden batch(es)


SELECT strftime('%Y-%m-%d %H-%M-%f','now')
strftime('%Y-%m-%d %H-%M-%f','now')
2017-12-27 21-15-08.492
 hidden batch(es)


SELECT MIN(chromosome) FROM dbsnp
MIN(chromosome)
TEST_1
 hidden batch(es)


EXPLAIN QUERY PLAN SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_1' ORDER BY chromosome LIMIT 1;
selectid order from detail
0 0 0 SEARCH TABLE dbsnp USING COVERING INDEX chromosome_IX (chromosome>?)
 hidden batch(es)


SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_1' ORDER BY chromosome LIMIT 1;
chromosome
TEST_2
 hidden batch(es)


EXPLAIN QUERY PLAN SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_2' ORDER BY chromosome LIMIT 1;
selectid order from detail
0 0 0 SEARCH TABLE dbsnp USING COVERING INDEX chromosome_IX (chromosome>?)
 hidden batch(es)


SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_2' ORDER BY chromosome LIMIT 1;
chromosome
TEST_3
 hidden batch(es)


SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_3' ORDER BY chromosome LIMIT 1;
chromosome
 hidden batch(es)


SELECT strftime('%Y-%m-%d %H-%M-%f','now')
strftime('%Y-%m-%d %H-%M-%f','now')
2017-12-27 21-15-08.496
 hidden batch(es)