Return to analyze8.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/analyze8.test, revision 1.1

1.1     ! misho       1: # 2011 August 13
        !             2: #
        !             3: # The author disclaims copyright to this source code.  In place of
        !             4: # a legal notice, here is a blessing:
        !             5: #
        !             6: #    May you do good and not evil.
        !             7: #    May you find forgiveness for yourself and forgive others.
        !             8: #    May you share freely, never taking more than you give.
        !             9: #
        !            10: #***********************************************************************
        !            11: #
        !            12: # This file implements tests for SQLite library.  The focus of the tests
        !            13: # in this file is testing the capabilities of sqlite_stat3.
        !            14: #
        !            15: 
        !            16: set testdir [file dirname $argv0]
        !            17: source $testdir/tester.tcl
        !            18: 
        !            19: ifcapable !stat3 {
        !            20:   finish_test
        !            21:   return
        !            22: }
        !            23: 
        !            24: set testprefix analyze8
        !            25: 
        !            26: proc eqp {sql {db db}} {
        !            27:   uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
        !            28: }
        !            29: 
        !            30: # Scenario:
        !            31: #
        !            32: #    Two indices.  One has mostly singleton entries, but for a few
        !            33: #    values there are hundreds of entries.  The other has 10-20
        !            34: #    entries per value.
        !            35: #
        !            36: # Verify that the query planner chooses the first index for the singleton
        !            37: # entries and the second index for the others.
        !            38: #
        !            39: do_test 1.0 {
        !            40:   db eval {
        !            41:     CREATE TABLE t1(a,b,c,d);
        !            42:     CREATE INDEX t1a ON t1(a);
        !            43:     CREATE INDEX t1b ON t1(b);
        !            44:     CREATE INDEX t1c ON t1(c);
        !            45:   }
        !            46:   for {set i 0} {$i<1000} {incr i} {
        !            47:     if {$i%2==0} {set a $i} {set a [expr {($i%8)*100}]}
        !            48:     set b [expr {$i/10}]
        !            49:     set c [expr {$i/8}]
        !            50:     set c [expr {$c*$c*$c}]
        !            51:     db eval {INSERT INTO t1 VALUES($a,$b,$c,$i)}
        !            52:   }
        !            53:   db eval {ANALYZE}
        !            54: } {}
        !            55: 
        !            56: # The a==100 comparison is expensive because there are many rows
        !            57: # with a==100.  And so for those cases, choose the t1b index.
        !            58: #
        !            59: # Buf ro a==99 and a==101, there are far fewer rows so choose
        !            60: # the t1a index.
        !            61: #
        !            62: do_test 1.1 {
        !            63:   eqp {SELECT * FROM t1 WHERE a=100 AND b=55}
        !            64: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
        !            65: do_test 1.2 {
        !            66:   eqp {SELECT * FROM t1 WHERE a=99 AND b=55}
        !            67: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
        !            68: do_test 1.3 {
        !            69:   eqp {SELECT * FROM t1 WHERE a=101 AND b=55}
        !            70: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
        !            71: do_test 1.4 {
        !            72:   eqp {SELECT * FROM t1 WHERE a=100 AND b=56}
        !            73: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
        !            74: do_test 1.5 {
        !            75:   eqp {SELECT * FROM t1 WHERE a=99 AND b=56}
        !            76: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
        !            77: do_test 1.6 {
        !            78:   eqp {SELECT * FROM t1 WHERE a=101 AND b=56}
        !            79: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
        !            80: do_test 2.1 {
        !            81:   eqp {SELECT * FROM t1 WHERE a=100 AND b BETWEEN 50 AND 54}
        !            82: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?) (~2 rows)}}
        !            83: 
        !            84: # There are many more values of c between 0 and 100000 than there are
        !            85: # between 800000 and 900000.  So t1c is more selective for the latter
        !            86: # range.
        !            87: #
        !            88: do_test 3.1 {
        !            89:   eqp {SELECT * FROM t1 WHERE b BETWEEN 50 AND 54 AND c BETWEEN 0 AND 100000}
        !            90: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?) (~6 rows)}}
        !            91: do_test 3.2 {
        !            92:   eqp {SELECT * FROM t1
        !            93:        WHERE b BETWEEN 50 AND 54 AND c BETWEEN 800000 AND 900000}
        !            94: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?) (~4 rows)}}
        !            95: do_test 3.3 {
        !            96:   eqp {SELECT * FROM t1 WHERE a=100 AND c BETWEEN 0 AND 100000}
        !            97: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~63 rows)}}
        !            98: do_test 3.4 {
        !            99:   eqp {SELECT * FROM t1
        !           100:        WHERE a=100 AND c BETWEEN 800000 AND 900000}
        !           101: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?) (~2 rows)}}
        !           102: 
        !           103: finish_test