Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE dbsnp ( > chromosome TEXT > table_constraint > ) > > <pre> > ✓ > </pre> <!-- --> > 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 > ) > > <pre> > ✓ > </pre> <!-- --> > 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 > ) > > <pre> > ✓ > </pre> <!-- --> > 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 > ) > > <pre> > ✓ > </pre> <!-- --> > SELECT COUNT(*) FROM dbsnp > > <pre> > | COUNT(*) | > | :------- | > | 4296875 | > </pre> <!-- --> > CREATE INDEX chromosome_IX ON dbsnp (chromosome) > > <pre> > ✓ > </pre> <!-- --> > EXPLAIN QUERY PLAN > SELECT DISTINCT chromosome FROM dbsnp > > <pre> > selectid | order | from | detail > :------- | :---- | :--- | :-------------------------------------------------- > 0 | 0 | 0 | SCAN TABLE dbsnp USING COVERING INDEX chromosome_IX > </pre> <!-- --> > SELECT strftime('%Y-%m-%d %H-%M-%f','now') > > <pre> > | strftime('%Y-%m-%d %H-%M-%f','now') | > | :---------------------------------- | > | 2017-12-27 21-15-08.175 | > </pre> <!-- --> > SELECT DISTINCT chromosome FROM dbsnp; > > <pre> > | chromosome | > | :--------- | > | TEST_1 | > | TEST_2 | > | TEST_3 | > </pre> <!-- --> > SELECT strftime('%Y-%m-%d %H-%M-%f','now') > > <pre> > | strftime('%Y-%m-%d %H-%M-%f','now') | > | :---------------------------------- | > | 2017-12-27 21-15-08.492 | > </pre> <!-- --> > EXPLAIN QUERY PLAN > SELECT MIN(chromosome) FROM dbsnp > > <pre> > selectid | order | from | detail > :------- | :---- | :--- | :---------------------------------------------------- > 0 | 0 | 0 | SEARCH TABLE dbsnp USING COVERING INDEX chromosome_IX > </pre> <!-- --> > SELECT strftime('%Y-%m-%d %H-%M-%f','now') > > <pre> > | strftime('%Y-%m-%d %H-%M-%f','now') | > | :---------------------------------- | > | 2017-12-27 21-15-08.492 | > </pre> <!-- --> > SELECT MIN(chromosome) FROM dbsnp > > <pre> > | MIN(chromosome) | > | :-------------- | > | TEST_1 | > </pre> <!-- --> > EXPLAIN QUERY PLAN > SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_1' ORDER BY chromosome LIMIT 1; > > <pre> > selectid | order | from | detail > :------- | :---- | :--- | :------------------------------------------------------------------- > 0 | 0 | 0 | SEARCH TABLE dbsnp USING COVERING INDEX chromosome_IX (chromosome&gt;?) > </pre> <!-- --> > SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_1' ORDER BY chromosome LIMIT 1; > > <pre> > | chromosome | > | :--------- | > | TEST_2 | > </pre> <!-- --> > EXPLAIN QUERY PLAN > SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_2' ORDER BY chromosome LIMIT 1; > > <pre> > selectid | order | from | detail > :------- | :---- | :--- | :------------------------------------------------------------------- > 0 | 0 | 0 | SEARCH TABLE dbsnp USING COVERING INDEX chromosome_IX (chromosome&gt;?) > </pre> <!-- --> > SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_2' ORDER BY chromosome LIMIT 1; > > <pre> > | chromosome | > | :--------- | > | TEST_3 | > </pre> <!-- --> > SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_3' ORDER BY chromosome LIMIT 1; > > <pre> > | chromosome | > | :--------- | > </pre> <!-- --> > SELECT strftime('%Y-%m-%d %H-%M-%f','now') > > <pre> > | strftime('%Y-%m-%d %H-%M-%f','now') | > | :---------------------------------- | > | 2017-12-27 21-15-08.496 | > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlite_3.8&fiddle=d6bd5909ff9fbaf7edb1083f1a582d1c)*
back to fiddle