1# 2014-04-26 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 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15set testprefix cost 16 17 18do_execsql_test 1.1 { 19 CREATE TABLE t3(id INTEGER PRIMARY KEY, b NOT NULL); 20 CREATE TABLE t4(c, d, e); 21 CREATE UNIQUE INDEX i3 ON t3(b); 22 CREATE UNIQUE INDEX i4 ON t4(c, d); 23} 24do_eqp_test 1.2 { 25 SELECT e FROM t3, t4 WHERE b=c ORDER BY b, d; 26} { 27 0 0 0 {SCAN TABLE t3 USING COVERING INDEX i3} 28 0 1 1 {SEARCH TABLE t4 USING INDEX i4 (c=?)} 29} 30 31 32do_execsql_test 2.1 { 33 CREATE TABLE t1(a, b); 34 CREATE INDEX i1 ON t1(a); 35} 36 37# It is better to use an index for ORDER BY than sort externally, even 38# if the index is a non-covering index. 39do_eqp_test 2.2 { 40 SELECT * FROM t1 ORDER BY a; 41} { 42 0 0 0 {SCAN TABLE t1 USING INDEX i1} 43} 44 45do_execsql_test 3.1 { 46 CREATE TABLE t5(a INTEGER PRIMARY KEY,b,c,d,e,f,g); 47 CREATE INDEX t5b ON t5(b); 48 CREATE INDEX t5c ON t5(c); 49 CREATE INDEX t5d ON t5(d); 50 CREATE INDEX t5e ON t5(e); 51 CREATE INDEX t5f ON t5(f); 52 CREATE INDEX t5g ON t5(g); 53} 54 55do_eqp_test 3.2 { 56 SELECT a FROM t5 57 WHERE b IS NULL OR c IS NULL OR d IS NULL 58 ORDER BY a; 59} { 60 0 0 0 {SEARCH TABLE t5 USING INDEX t5b (b=?)} 61 0 0 0 {SEARCH TABLE t5 USING INDEX t5c (c=?)} 62 0 0 0 {SEARCH TABLE t5 USING INDEX t5d (d=?)} 63 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 64} 65 66 67 68finish_test 69