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

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